Solved

Help with pl-sql query

Posted on 2014-01-29
3
592 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 73

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

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

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

Suggested Solutions

Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
Via a live example, show how to take different types of Oracle backups using RMAN.

777 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