Solved

Help with pl-sql query

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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

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…
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
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.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

730 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