Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 616
  • Last Modified:

Help with pl-sql query

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
parpaa
Asked:
parpaa
2 Solutions
 
slightwv (䄆 Netminder) Commented:
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
 
sdstuberCommented:
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
 
parpaaAuthor Commented:
Thank you guys.
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now