Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

Help with pl-sql query

Posted on 2014-01-29
3
594 Views
Last Modified: 2014-01-29
Hello experts,

I am trying to build a report which will get the current count from each queue. However if it doesnt have a count, the queue is not listed. But i wanted to list all queues from my IN list in the query below and return zero if it doesnt have any count. Please see below my actual resultset and desired resultset. how can i change my below logic and make it look the way i wanted? Any easy suggestion?


select IWD_EXT_CUSTOMERSEGMENT as Virtual_queue,count(case when floor(((sysdate-received_at)*24*60*60)/3600)
between 0 and 12 then IWD_EXT_CUSTOMERSEGMENT else null end) open_less_than_12_hrs, count(case when floor(((sysdate-received_at)*24*60*60)/3600)
between 13 and 48 then IWD_EXT_CUSTOMERSEGMENT else null end) open_13_to_48_hrs, count(case when floor(((sysdate-received_at)*24*60*60)/3600) between 49 and 96
then IWD_EXT_CUSTOMERSEGMENT else null end) open_49_to_96_hrs, count(case when floor(((sysdate-received_at)*24*60*60)/3600) > 96 then IWD_EXT_CUSTOMERSEGMENT else null end)
open_greater_than_96_hrs FROM interactions where received_at between trunc(sysdate-1) and trunc(sysdate) and queue= 'ATTACK_Queued'
and assigned_to is null
and IWD_EXT_CUSTOMERSEGMENT in  ('A','B','C','D','E')
group by IWD_EXT_CUSTOMERSEGMENT order by IWD_EXT_CUSTOMERSEGMENT;

Current Result Set:
Virtaul_queue     Open_lessthan_hr   open_13_48  open 49_96_hr  open_grter_than_96_hr
A                          2                                 0                    6                          34
B                          5                                   2                  1                           23

Desired Result Set:

Virtaul_queue     Open_lessthan_hr   open_13_48  open 49_96_hr  open_grter_than_96_hr
A                          2                                 0                    6                          34
B                          5                                   2                  1                           23
C                         0                                   0                   0                           0
D                         0                                   0                   0                           0
E                        0                                   0                   0                           0
0
Comment
Question by:parpaa
3 Comments
 
LVL 76

Accepted Solution

by:
slightwv (䄆 Netminder) earned 250 total points
ID: 39819078
You basically need to generate a result set that has ALL the values then join to it.

There are many ways.

See if this test case helps.  It is a little simplified but shows the basics.

drop table tab1 purge;
create table tab1(col1 char(1), col2 number);

insert into tab1 values('a',1);
insert into tab1 values('b',2);
commit;

with mydata as (
select 'a,b,c,d' mystring from dual
)
select mystring, nvl(sum(col2),0)
from tab1 t1, 
(
	select rtrim(regexp_substr(mystring,'([[:alnum:]]*)(,)?',1,column_value),',') mystring
	from mydata,
 	table(
  	cast(
  	multiset(select level from dual connect by level <= ((length(mystring)-length(replace(mystring,','))))+1)
  	as sys.odcivarchar2list
  	)
  	)
) mylist
where mylist.mystring=col1(+)
group by mystring
/

Open in new window

0
 
LVL 74

Assisted Solution

by:sdstuber
sdstuber earned 250 total points
ID: 39819084
try creating an outer join to your list instead of an IN list

  SELECT x AS virtual_queue,
         COUNT(
             CASE
                 WHEN FLOOR(((SYSDATE - received_at) * 24 * 60 * 60) / 3600) BETWEEN 0 AND 12
                 THEN
                     iwd_ext_customersegment
                 ELSE
                     NULL
             END
         )
             open_less_than_12_hrs,
         COUNT(
             CASE
                 WHEN FLOOR(((SYSDATE - received_at) * 24 * 60 * 60) / 3600) BETWEEN 13 AND 48
                 THEN
                     iwd_ext_customersegment
                 ELSE
                     NULL
             END
         )
             open_13_to_48_hrs,
         COUNT(
             CASE
                 WHEN FLOOR(((SYSDATE - received_at) * 24 * 60 * 60) / 3600) BETWEEN 49 AND 96
                 THEN
                     iwd_ext_customersegment
                 ELSE
                     NULL
             END
         )
             open_49_to_96_hrs,
         COUNT(
             CASE
                 WHEN FLOOR(((SYSDATE - received_at) * 24 * 60 * 60) / 3600) > 96
                 THEN
                     iwd_ext_customersegment
                 ELSE
                     NULL
             END
         )
             open_greater_than_96_hrs
    FROM interactions
         RIGHT JOIN (SELECT 'A' x FROM DUAL
                     UNION ALL
                     SELECT 'B' FROM DUAL
                     UNION ALL
                     SELECT 'C' FROM DUAL
                     UNION ALL
                     SELECT 'D' FROM DUAL
                     UNION ALL
                     SELECT 'E' FROM DUAL)
             ON iwd_ext_customersegment = x
   WHERE received_at BETWEEN TRUNC(SYSDATE - 1) AND TRUNC(SYSDATE)
     AND queue = 'ATTACK_Queued'
     AND assigned_to IS NULL
GROUP BY x
ORDER BY x;


alternate version using collection


  SELECT COLUMN_VALUE AS virtual_queue,
         COUNT(
             CASE
                 WHEN FLOOR(((SYSDATE - received_at) * 24 * 60 * 60) / 3600) BETWEEN 0 AND 12
                 THEN
                     iwd_ext_customersegment
                 ELSE
                     NULL
             END
         )
             open_less_than_12_hrs,
         COUNT(
             CASE
                 WHEN FLOOR(((SYSDATE - received_at) * 24 * 60 * 60) / 3600) BETWEEN 13 AND 48
                 THEN
                     iwd_ext_customersegment
                 ELSE
                     NULL
             END
         )
             open_13_to_48_hrs,
         COUNT(
             CASE
                 WHEN FLOOR(((SYSDATE - received_at) * 24 * 60 * 60) / 3600) BETWEEN 49 AND 96
                 THEN
                     iwd_ext_customersegment
                 ELSE
                     NULL
             END
         )
             open_49_to_96_hrs,
         COUNT(
             CASE
                 WHEN FLOOR(((SYSDATE - received_at) * 24 * 60 * 60) / 3600) > 96
                 THEN
                     iwd_ext_customersegment
                 ELSE
                     NULL
             END
         )
             open_greater_than_96_hrs
    FROM interactions
         RIGHT JOIN TABLE(
                        sys.ora_mining_varchar2_nt(
                            'A',
                            'B',
                            'C',
                            'D',
                            'E'
                        )
                    )
             ON iwd_ext_customersegment = COLUMN_VALUE
   WHERE received_at BETWEEN TRUNC(SYSDATE - 1) AND TRUNC(SYSDATE)
     AND queue = 'ATTACK_Queued'
     AND assigned_to IS NULL
GROUP BY column_value
ORDER BY column_value;
0
 

Author Closing Comment

by:parpaa
ID: 39819136
Thank you guys.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
Via a live example, show how to take different types of Oracle backups using RMAN.
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.

860 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question