[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

Help with pl-sql query

Posted on 2014-01-29
3
Medium Priority
?
610 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
[X]
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
3 Comments
 
LVL 77

Accepted Solution

by:
slightwv (䄆 Netminder) earned 1000 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 1000 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

What’s Wrong with Your Cloud Strategy ?

Even as many CIOs are embracing a cloud-first strategy, the reality is that moving to the cloud is a lengthy process and the end-state is likely to be a blend of multiple clouds—public and private. Learn why multicloud solutions matter in this webinar by Nimble Storage.

Question has a verified solution.

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

If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
Shell script to create broker configuration file using current broker Configuration, solely for purpose of backup on Linux. Script may need to be modified depending on OS-installation. Please deploy and verify the script in a test environment.
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.
This video shows how to recover a database from a user managed backup

656 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