Home » SQL & PL/SQL » SQL & PL/SQL » Problem: Need help! (Oracle 9i)
Problem: Need help! [message #331646] |
Fri, 04 July 2008 05:47  |
Olivia
Messages: 519 Registered: June 2008
|
Senior Member |
|
|
Problem:
Get the queue count for all sub application for a particular application.
The output hierarchy should be like this:
For application XAPP1:
Application: XAPP1
SubApp Location QueueCd Queue Total
_____________________________
XSAP1 LOCATION1(10) MQ6 Queue6 1
LOCATION1(10) MQ7 Queue7 1
LOCATION2(11) MQ7 Queue7 4
XSAP2 MQ8 Queue8 3
....
....
Application: XSAP5
Location Queuecd Queue Total
LOCATION(11) MQ8 Queue8 1
LOCATION(80) MQ8 Queue8 1
MQ10 Queue10 1
TABLE A:
CAPT CODE DESCRIPTION PRIOR_FIELD UPDT_TMS
---- ---------- ---- -------------------------------------------------- ---- ---------------------
CBO1 XAPP1 ApplicationX ROOT 29-MAY-08 11.44.10 AM
CBO2 XSAP1 Sub application1(X) XAPP1 29-MAY-08 11.57.59 AM
CBO2 XSAP2 Sub application2(X) XAPP1 29-MAY-08 12.00.24 PM
CBO2 XSAP3 Sub application3(X) XAPP1 30-MAY-08 12.19.56 AM
CBO2 XSAP4 Sub application4(X) XAPP1 30-MAY-08 12.20.04 AM
CBO2 XSAP5 Sub application5(X) XAPP1 30-MAY-08 12.20.10 AM
CBO1 YAPP1 ApplicationY ROOT 29-MAY-08 11.44.10 AM
CBO2 YSAP1 Sub application1(Y) YAPP1 29-MAY-08 11.57.59 AM
CBO2 YSAP2 Sub application2(Y) YAPP1 29-MAY-08 12.00.24 PM
CBO2 YSAP3 Sub application3(Y) YAPP1 30-MAY-08 12.19.56 AM
CBO2 YSAP4 Sub application4(Y) YAPP1 30-MAY-08 12.20.04 AM
CBO1 ZAPP1 ApplicationZ (No sub appl) ROOT 29-MAY-08 11.44.10 AM
CBO3 MQ1 Queue1 YSAP1 29-MAY-08 11.57.59 AM
CBO3 MQ2 Queue2 YSAP1 29-MAY-08 12.00.24 PM
CBO3 MQ3 Queue3 YSAP1 30-MAY-08 12.19.56 AM
CBO3 MQ4 Queue4 YSAP1 30-MAY-08 12.20.04 AM
CBO3 MQ5 Queue5 YSAP1 30-MAY-08 12.20.10 AM
CBO3 MQ6 Queue6 XSAP1 29-MAY-08 11.57.59 AM
CBO3 MQ7 Queue7 XSAP1 29-MAY-08 12.00.24 PM
CBO3 MQ8 Queue8 XSAP2 30-MAY-08 12.19.56 AM
CBO3 MQ9 Queue9 XSAP3 30-MAY-08 12.20.04 AM
CBO3 MQ10 Queue10 XSAP4 30-MAY-08 12.20.10 AM
CBO3 MQ9 Queue9 ZAPP1 30-MAY-08 12.20.04 AM
CBO3 MQ10 Queue10 ZAPP1 30-MAY-08 12.20.10 AM
TABLE B:
CODE UPDT_TMS IND LOCATION_ID
---- ---------- ---- -------------------------------------------------- ---- ---------------------
MQ1 29-MAY-08 11.44.10 AM Y 10
MQ2 29-MAY-08 11.57.59 AM N 20
MQ4 29-MAY-08 12.00.24 PM Y 20
MQ4 30-MAY-08 12.19.56 AM Y 40
MQ3 30-MAY-08 12.20.04 AM N 70
MQ8 30-MAY-08 12.20.10 AM N 70
MQ9 29-MAY-08 11.44.10 AM 80
MQ1 29-MAY-08 11.57.59 AM
MQ2 29-MAY-08 12.00.24 PM
MQ8 12-MAY-08 12.20.10 AM N 70
MQ8 10-MAY-08 02.20.10 AM N 70
MQ5 30-MAY-08 12.19.56 AM N 11
MQ7 30-MAY-08 12.20.04 AM N 11
MQ7 30-MAY-08 12.20.04 AM N 11
MQ7 20-MAY-08 12.20.04 AM N 11
MQ7 29-MAY-08 12.20.04 AM N 11
MQ9 29-MAY-08 11.57.59 AM N 11
MQ10 29-MAY-08 12.00.24 PM Y 80
MQ6 30-MAY-08 12.19.56 AM Y 10
MQ7 30-MAY-08 12.20.04 AM Y 10
MQ4 30-MAY-08 12.20.10 AM N
Regards,
Oli
[Updated on: Fri, 04 July 2008 06:13] by Moderator Report message to a moderator
|
|
|
Re: Problem: Need help! [message #331657 is a reply to message #331646] |
Fri, 04 July 2008 06:56   |
Olivia
Messages: 519 Registered: June 2008
|
Senior Member |
|
|
I tried this way...
CREATE OR REPLACE PROCEDURE PROC_GET_COUNT
( p_application_cd IN TABLE_A.PRIOR_FIELD%TYPE,
p_sub_app_cd IN TABLE_A.PRIOR_FIELD%TYPE DEFAULT NULL,
p_loc_cd IN LOCATION.LOCATION_ID%TYPE,
p_queue_cd IN TABLE_A.CODE%TYPE DEFAULT NULL,
RC OUT gPkg.RCT)
IS
BEGIN
OPEN RC FOR
SELECT A.PRIOR_FIELD,B.LOCATION_ID,C.LOC_NAME AS LOCATION, A.CODE AS QUEUE_CODE,A.DESCRIPTION AS QUEUE,
COUNT(*) AS CNT
FROM TABLE_A A, TABLE_B B,LOCATION C
WHERE B.LOCATION_ID = C.LOCATION_ID(+)
AND A.CODE = B.CODE
--// Pass application name if there is no sub application else pass sub application name
AND (( p_sub_app_cd IS NULL AND B.PRIOR_FIELD = p_application_cd)
OR ( p_sub_app_cd IS NOT NULL AND B.PRIOR_FIELD = p_sub_app_cd))
---// Pass location
AND ( p_loc_CD IS NULL OR ( p_loc_CD IS NOT NULL AND A.LOCATION_ID = p_loc_CD))
-- //Pass profile
GROUP BY A.PRIOR_FIELD,B.LOCATION_ID,C.LOC_NAME, A.CODE,A.DESCRIPTION;
END;
Initially, requirement was something like below:
If application doesnot contain sub application then show queue count based on Application > Loacation level > Queuewise details
For a particular application, pass the sub application name and get queuewise count for different locations
But now, requirement is .. For a particular application, select all sub application and get the queuewise(queue1,queue2 etc) counts for different locations
How I would modify the below query. Need your suggesion.
AND (( p_sub_app_cd IS NULL AND B.PRIOR_FIELD = p_application_cd)
OR ( p_sub_app_cd IS NOT NULL AND B.PRIOR_FIELD = p_sub_app_cd))
Requirement:
________________
TABLE A:
CAPT CODE DESCRIPTION PRIOR_FIELD UPDT_TMS
---- ---------- ---- -------------------------------------------------- ---- ---------------------
CBO1 XAPP1 ApplicationX ROOT 29-MAY-08 11.44.10 AM
CBO2 XSAP1 Sub application1(X) XAPP1 29-MAY-08 11.57.59 AM
CBO2 XSAP2 Sub application2(X) XAPP1 29-MAY-08 12.00.24 PM
CBO2 XSAP3 Sub application3(X) XAPP1 30-MAY-08 12.19.56 AM
CBO2 XSAP4 Sub application4(X) XAPP1 30-MAY-08 12.20.04 AM
CBO2 XSAP5 Sub application5(X) XAPP1 30-MAY-08 12.20.10 AM
CBO1 YAPP1 ApplicationY ROOT 29-MAY-08 11.44.10 AM
CBO2 YSAP1 Sub application1(Y) YAPP1 29-MAY-08 11.57.59 AM
CBO2 YSAP2 Sub application2(Y) YAPP1 29-MAY-08 12.00.24 PM
CBO2 YSAP3 Sub application3(Y) YAPP1 30-MAY-08 12.19.56 AM
CBO2 YSAP4 Sub application4(Y) YAPP1 30-MAY-08 12.20.04 AM
CBO1 ZAPP1 ApplicationZ (No sub appl) ROOT 29-MAY-08 11.44.10 AM
CBO3 MQ1 Queue1 YSAP1 29-MAY-08 11.57.59 AM
CBO3 MQ2 Queue2 YSAP1 29-MAY-08 12.00.24 PM
CBO3 MQ3 Queue3 YSAP1 30-MAY-08 12.19.56 AM
CBO3 MQ4 Queue4 YSAP1 30-MAY-08 12.20.04 AM
CBO3 MQ5 Queue5 YSAP1 30-MAY-08 12.20.10 AM
CBO3 MQ6 Queue6 XSAP1 29-MAY-08 11.57.59 AM
CBO3 MQ7 Queue7 XSAP1 29-MAY-08 12.00.24 PM
CBO3 MQ8 Queue8 XSAP2 30-MAY-08 12.19.56 AM
CBO3 MQ9 Queue9 XSAP3 30-MAY-08 12.20.04 AM
CBO3 MQ10 Queue10 XSAP4 30-MAY-08 12.20.10 AM
CBO3 MQ9 Queue9 ZAPP1 30-MAY-08 12.20.04 AM
CBO3 MQ10 Queue10 ZAPP1 30-MAY-08 12.20.10 AM
TABLE B:
CODE UPDT_TMS IND LOCATION_ID
---- ---------- ---- -------------------------------------------------- ---- ---------------------
MQ1 29-MAY-08 11.44.10 AM Y 10
MQ2 29-MAY-08 11.57.59 AM N 20
MQ4 29-MAY-08 12.00.24 PM Y 20
MQ4 30-MAY-08 12.19.56 AM Y 40
MQ3 30-MAY-08 12.20.04 AM N 70
MQ8 30-MAY-08 12.20.10 AM N 70
MQ9 29-MAY-08 11.44.10 AM 80
MQ1 29-MAY-08 11.57.59 AM
MQ2 29-MAY-08 12.00.24 PM
MQ8 12-MAY-08 12.20.10 AM N 70
MQ8 10-MAY-08 02.20.10 AM N 70
MQ5 30-MAY-08 12.19.56 AM N 11
MQ7 30-MAY-08 12.20.04 AM N 11
MQ7 30-MAY-08 12.20.04 AM N 11
MQ7 20-MAY-08 12.20.04 AM N 11
MQ7 29-MAY-08 12.20.04 AM N 11
MQ9 29-MAY-08 11.57.59 AM N 11
MQ10 29-MAY-08 12.00.24 PM Y 80
MQ6 30-MAY-08 12.19.56 AM Y 10
MQ7 30-MAY-08 12.20.04 AM Y 10
MQ4 30-MAY-08 12.20.10 AM N
Problem:
Get the queue count for all sub application for a particular application.
The output hierarchy should be like this:
For application XAPP1:
Application: XAPP1
SubApp Location QueueCd Queue Total
_____________________________
XSAP1 LOCATION1(10) MQ6 Queue6 1
LOCATION1(10) MQ7 Queue7 1
LOCATION2(11) MQ7 Queue7 4
XSAP2 MQ8 Queue8 3
....
....
Another result may be..
<--If application have no sub application ->
Application: ZAPP1
SubApp Location QueueCd Queue Total
_____________________________
LOCATION1(11) MQ9 Queue9 1
LOCATION1(80) MQ9 Queue9 1
MQ10 Queue10 1
....
[Updated on: Fri, 04 July 2008 07:11] Report message to a moderator
|
|
|
Re: Problem: Need help! [message #331863 is a reply to message #331657] |
Sun, 06 July 2008 02:33  |
prtz
Messages: 11 Registered: January 2008
|
Junior Member |
|
|
> The output hierarchy should be like this:
>
> For application XAPP1:
>
> Application: XAPP1
>
> SubApp Location QueueCd Queue Total
> _____________________________
>
> XSAP1 LOCATION1(10) MQ6 Queue6 1
> LOCATION1(10) MQ7 Queue7 1
> LOCATION2(11) MQ7 Queue7 4
> XSAP2 MQ8 Queue8 3
>
> ....
>
> ....
Maybe something like this ?
SQL>
SQL>
--
with table_a as (
select 'CBO1' capt, 'XAPP1' code, 'ApplicationX' description, 'ROOT' prior_field, to_date('29-MAY-08 11.44.10 AM','DD-MON-RR HH.MI.SS AM') updt_tms from dual union all
select 'CBO2', 'XSAP1', 'Sub application1(X)', 'XAPP1', to_date('29-MAY-08 11.57.59 AM','DD-MON-RR HH.MI.SS AM') from dual union all
select 'CBO2', 'XSAP2', 'Sub application2(X)', 'XAPP1', to_date('29-MAY-08 12.00.24 PM','DD-MON-RR HH.MI.SS AM') from dual union all
select 'CBO2', 'XSAP3', 'Sub application3(X)', 'XAPP1', to_date('30-MAY-08 12.19.56 AM','DD-MON-RR HH.MI.SS AM') from dual union all
select 'CBO2', 'XSAP4', 'Sub application4(X)', 'XAPP1', to_date('30-MAY-08 12.20.04 AM','DD-MON-RR HH.MI.SS AM') from dual union all
select 'CBO2', 'XSAP5', 'Sub application5(X)', 'XAPP1', to_date('30-MAY-08 12.20.10 AM','DD-MON-RR HH.MI.SS AM') from dual union all
select 'CBO1', 'YAPP1', 'ApplicationY', 'ROOT', to_date('29-MAY-08 11.44.10 AM','DD-MON-RR HH.MI.SS AM') from dual union all
select 'CBO2', 'YSAP1', 'Sub application1(Y)', 'YAPP1', to_date('29-MAY-08 11.57.59 AM','DD-MON-RR HH.MI.SS AM') from dual union all
select 'CBO2', 'YSAP2', 'Sub application2(Y)', 'YAPP1', to_date('29-MAY-08 12.00.24 PM','DD-MON-RR HH.MI.SS AM') from dual union all
select 'CBO2', 'YSAP3', 'Sub application3(Y)', 'YAPP1', to_date('30-MAY-08 12.19.56 AM','DD-MON-RR HH.MI.SS AM') from dual union all
select 'CBO2', 'YSAP4', 'Sub application4(Y)', 'YAPP1', to_date('30-MAY-08 12.20.04 AM','DD-MON-RR HH.MI.SS AM') from dual union all
select 'CBO1', 'ZAPP1', 'ApplicationZ (No sub appl)', 'ROOT', to_date('29-MAY-08 11.44.10 AM','DD-MON-RR HH.MI.SS AM') from dual union all
select 'CBO3', 'MQ1', 'Queue1', 'YSAP1', to_date('29-MAY-08 11.57.59 AM','DD-MON-RR HH.MI.SS AM') from dual union all
select 'CBO3', 'MQ2', 'Queue2', 'YSAP1', to_date('29-MAY-08 12.00.24 PM','DD-MON-RR HH.MI.SS AM') from dual union all
select 'CBO3', 'MQ3', 'Queue3', 'YSAP1', to_date('30-MAY-08 12.19.56 AM','DD-MON-RR HH.MI.SS AM') from dual union all
select 'CBO3', 'MQ4', 'Queue4', 'YSAP1', to_date('30-MAY-08 12.20.04 AM','DD-MON-RR HH.MI.SS AM') from dual union all
select 'CBO3', 'MQ5', 'Queue5', 'YSAP1', to_date('30-MAY-08 12.20.10 AM','DD-MON-RR HH.MI.SS AM') from dual union all
select 'CBO3', 'MQ6', 'Queue6', 'XSAP1', to_date('29-MAY-08 11.57.59 AM','DD-MON-RR HH.MI.SS AM') from dual union all
select 'CBO3', 'MQ7', 'Queue7', 'XSAP1', to_date('29-MAY-08 12.00.24 PM','DD-MON-RR HH.MI.SS AM') from dual union all
select 'CBO3', 'MQ8', 'Queue8', 'XSAP2', to_date('30-MAY-08 12.19.56 AM','DD-MON-RR HH.MI.SS AM') from dual union all
select 'CBO3', 'MQ9', 'Queue9', 'XSAP3', to_date('30-MAY-08 12.20.04 AM','DD-MON-RR HH.MI.SS AM') from dual union all
select 'CBO3', 'MQ10', 'Queue10', 'XSAP4', to_date('30-MAY-08 12.20.10 AM','DD-MON-RR HH.MI.SS AM') from dual union all
select 'CBO3', 'MQ9', 'Queue9', 'ZAPP1', to_date('30-MAY-08 12.20.04 AM','DD-MON-RR HH.MI.SS AM') from dual union all
select 'CBO3', 'MQ10', 'Queue10', 'ZAPP1', to_date('30-MAY-08 12.20.10 AM','DD-MON-RR HH.MI.SS AM') from dual),
table_b as (
select 'MQ1' code, to_date('29-MAY-08 11.44.10 AM','DD-MON-RR HH.MI.SS AM') updt_tms, 'Y' ind, 10 location_id from dual union all
select 'MQ2', to_date('29-MAY-08 11.57.59 AM','DD-MON-RR HH.MI.SS AM'), 'N', 20 from dual union all
select 'MQ4', to_date('29-MAY-08 12.00.24 PM','DD-MON-RR HH.MI.SS AM'), 'Y', 20 from dual union all
select 'MQ4', to_date('30-MAY-08 12.19.56 AM','DD-MON-RR HH.MI.SS AM'), 'Y', 40 from dual union all
select 'MQ3', to_date('30-MAY-08 12.20.04 AM','DD-MON-RR HH.MI.SS AM'), 'N', 70 from dual union all
select 'MQ8', to_date('30-MAY-08 12.20.10 AM','DD-MON-RR HH.MI.SS AM'), 'N', 70 from dual union all
select 'MQ9', to_date('29-MAY-08 11.44.10 AM','DD-MON-RR HH.MI.SS AM'), '', 80 from dual union all
select 'MQ1', to_date('29-MAY-08 11.57.59 AM','DD-MON-RR HH.MI.SS AM'), '', null from dual union all
select 'MQ2', to_date('29-MAY-08 12.00.24 PM','DD-MON-RR HH.MI.SS AM'), '', null from dual union all
select 'MQ8', to_date('12-MAY-08 12.20.10 AM','DD-MON-RR HH.MI.SS AM'), 'N', 70 from dual union all
select 'MQ8', to_date('10-MAY-08 02.20.10 AM','DD-MON-RR HH.MI.SS AM'), 'N', 70 from dual union all
select 'MQ5', to_date('30-MAY-08 12.19.56 AM','DD-MON-RR HH.MI.SS AM'), 'N', 11 from dual union all
select 'MQ7', to_date('30-MAY-08 12.20.04 AM','DD-MON-RR HH.MI.SS AM'), 'N', 11 from dual union all
select 'MQ7', to_date('30-MAY-08 12.20.04 AM','DD-MON-RR HH.MI.SS AM'), 'N', 11 from dual union all
select 'MQ7', to_date('20-MAY-08 12.20.04 AM','DD-MON-RR HH.MI.SS AM'), 'N', 11 from dual union all
select 'MQ7', to_date('29-MAY-08 12.20.04 AM','DD-MON-RR HH.MI.SS AM'), 'N', 11 from dual union all
select 'MQ9', to_date('29-MAY-08 11.57.59 AM','DD-MON-RR HH.MI.SS AM'), 'N', 11 from dual union all
select 'MQ10', to_date('29-MAY-08 12.00.24 PM','DD-MON-RR HH.MI.SS AM'), 'Y', 80 from dual union all
select 'MQ6', to_date('30-MAY-08 12.19.56 AM','DD-MON-RR HH.MI.SS AM'), 'Y', 10 from dual union all
select 'MQ7', to_date('30-MAY-08 12.20.04 AM','DD-MON-RR HH.MI.SS AM'), 'Y', 10 from dual union all
select 'MQ4', to_date('30-MAY-08 12.20.10 AM','DD-MON-RR HH.MI.SS AM'), 'N', null from dual)
--
select
application,
subapp,
'LOCATION'||
row_number() over (partition by application,subapp,queuecd order by application,subapp,queuecd,location)||
'('||location||')' as location,
queuecd,
'Queue'||replace(queuecd,'MQ') as queue,
total
from (
select
a.application,
a.subapp,
b.location_id as location,
b.code as queuecd,
count(*) as total
from (
select
connect_by_root code as application,
prior_field as subapp,
code as ccode,
level as lvl
from table_a
start with prior_field = 'ROOT'
connect by prior_field = prior code) a,
table_b b
where a.ccode = b.code
and a.lvl = 3
group by a.application, a.subapp, b.location_id, b.code
)
order by application, subapp, to_number(replace(queuecd,'MQ')), location;
APPLICATION SUBAPP LOCATION QUEUECD QUEUE TOTAL
------------ -------- --------------- -------- --------- ----------
XAPP1 XSAP1 LOCATION1(10) MQ6 Queue6 1
XAPP1 XSAP1 LOCATION1(10) MQ7 Queue7 1
XAPP1 XSAP1 LOCATION2(11) MQ7 Queue7 4
XAPP1 XSAP2 LOCATION1(70) MQ8 Queue8 3
XAPP1 XSAP3 LOCATION1(11) MQ9 Queue9 1
XAPP1 XSAP3 LOCATION2(80) MQ9 Queue9 1
XAPP1 XSAP4 LOCATION1(80) MQ10 Queue10 1
YAPP1 YSAP1 LOCATION1(10) MQ1 Queue1 1
YAPP1 YSAP1 LOCATION2() MQ1 Queue1 1
YAPP1 YSAP1 LOCATION1(20) MQ2 Queue2 1
YAPP1 YSAP1 LOCATION2() MQ2 Queue2 1
YAPP1 YSAP1 LOCATION1(70) MQ3 Queue3 1
YAPP1 YSAP1 LOCATION1(20) MQ4 Queue4 1
YAPP1 YSAP1 LOCATION2(40) MQ4 Queue4 1
YAPP1 YSAP1 LOCATION3() MQ4 Queue4 1
YAPP1 YSAP1 LOCATION1(11) MQ5 Queue5 1
16 rows selected.
SQL>
SQL>
SQL>
=======================================================================
> Another result may be..
>
>
>
>
> <--If application have no sub application ->
> Application: ZAPP1
>
>
> SubApp Location QueueCd Queue Total
> _____________________________
>
> LOCATION1(11) MQ9 Queue9 1
> LOCATION1(80) MQ9 Queue9 1
> MQ10 Queue10 1
>
>
> ....
>
This could be achieved by a small variation on the query posted above:
SQL>
SQL>
--
with table_a as (
select 'CBO1' capt, 'XAPP1' code, 'ApplicationX' description, 'ROOT' prior_field, to_date('29-MAY-08 11.44.10 AM','DD-MON-RR HH.MI.SS AM') updt_tms from dual union all
select 'CBO2', 'XSAP1', 'Sub application1(X)', 'XAPP1', to_date('29-MAY-08 11.57.59 AM','DD-MON-RR HH.MI.SS AM') from dual union all
select 'CBO2', 'XSAP2', 'Sub application2(X)', 'XAPP1', to_date('29-MAY-08 12.00.24 PM','DD-MON-RR HH.MI.SS AM') from dual union all
select 'CBO2', 'XSAP3', 'Sub application3(X)', 'XAPP1', to_date('30-MAY-08 12.19.56 AM','DD-MON-RR HH.MI.SS AM') from dual union all
select 'CBO2', 'XSAP4', 'Sub application4(X)', 'XAPP1', to_date('30-MAY-08 12.20.04 AM','DD-MON-RR HH.MI.SS AM') from dual union all
select 'CBO2', 'XSAP5', 'Sub application5(X)', 'XAPP1', to_date('30-MAY-08 12.20.10 AM','DD-MON-RR HH.MI.SS AM') from dual union all
select 'CBO1', 'YAPP1', 'ApplicationY', 'ROOT', to_date('29-MAY-08 11.44.10 AM','DD-MON-RR HH.MI.SS AM') from dual union all
select 'CBO2', 'YSAP1', 'Sub application1(Y)', 'YAPP1', to_date('29-MAY-08 11.57.59 AM','DD-MON-RR HH.MI.SS AM') from dual union all
select 'CBO2', 'YSAP2', 'Sub application2(Y)', 'YAPP1', to_date('29-MAY-08 12.00.24 PM','DD-MON-RR HH.MI.SS AM') from dual union all
select 'CBO2', 'YSAP3', 'Sub application3(Y)', 'YAPP1', to_date('30-MAY-08 12.19.56 AM','DD-MON-RR HH.MI.SS AM') from dual union all
select 'CBO2', 'YSAP4', 'Sub application4(Y)', 'YAPP1', to_date('30-MAY-08 12.20.04 AM','DD-MON-RR HH.MI.SS AM') from dual union all
select 'CBO1', 'ZAPP1', 'ApplicationZ (No sub appl)', 'ROOT', to_date('29-MAY-08 11.44.10 AM','DD-MON-RR HH.MI.SS AM') from dual union all
select 'CBO3', 'MQ1', 'Queue1', 'YSAP1', to_date('29-MAY-08 11.57.59 AM','DD-MON-RR HH.MI.SS AM') from dual union all
select 'CBO3', 'MQ2', 'Queue2', 'YSAP1', to_date('29-MAY-08 12.00.24 PM','DD-MON-RR HH.MI.SS AM') from dual union all
select 'CBO3', 'MQ3', 'Queue3', 'YSAP1', to_date('30-MAY-08 12.19.56 AM','DD-MON-RR HH.MI.SS AM') from dual union all
select 'CBO3', 'MQ4', 'Queue4', 'YSAP1', to_date('30-MAY-08 12.20.04 AM','DD-MON-RR HH.MI.SS AM') from dual union all
select 'CBO3', 'MQ5', 'Queue5', 'YSAP1', to_date('30-MAY-08 12.20.10 AM','DD-MON-RR HH.MI.SS AM') from dual union all
select 'CBO3', 'MQ6', 'Queue6', 'XSAP1', to_date('29-MAY-08 11.57.59 AM','DD-MON-RR HH.MI.SS AM') from dual union all
select 'CBO3', 'MQ7', 'Queue7', 'XSAP1', to_date('29-MAY-08 12.00.24 PM','DD-MON-RR HH.MI.SS AM') from dual union all
select 'CBO3', 'MQ8', 'Queue8', 'XSAP2', to_date('30-MAY-08 12.19.56 AM','DD-MON-RR HH.MI.SS AM') from dual union all
select 'CBO3', 'MQ9', 'Queue9', 'XSAP3', to_date('30-MAY-08 12.20.04 AM','DD-MON-RR HH.MI.SS AM') from dual union all
select 'CBO3', 'MQ10', 'Queue10', 'XSAP4', to_date('30-MAY-08 12.20.10 AM','DD-MON-RR HH.MI.SS AM') from dual union all
select 'CBO3', 'MQ9', 'Queue9', 'ZAPP1', to_date('30-MAY-08 12.20.04 AM','DD-MON-RR HH.MI.SS AM') from dual union all
select 'CBO3', 'MQ10', 'Queue10', 'ZAPP1', to_date('30-MAY-08 12.20.10 AM','DD-MON-RR HH.MI.SS AM') from dual),
table_b as (
select 'MQ1' code, to_date('29-MAY-08 11.44.10 AM','DD-MON-RR HH.MI.SS AM') updt_tms, 'Y' ind, 10 location_id from dual union all
select 'MQ2', to_date('29-MAY-08 11.57.59 AM','DD-MON-RR HH.MI.SS AM'), 'N', 20 from dual union all
select 'MQ4', to_date('29-MAY-08 12.00.24 PM','DD-MON-RR HH.MI.SS AM'), 'Y', 20 from dual union all
select 'MQ4', to_date('30-MAY-08 12.19.56 AM','DD-MON-RR HH.MI.SS AM'), 'Y', 40 from dual union all
select 'MQ3', to_date('30-MAY-08 12.20.04 AM','DD-MON-RR HH.MI.SS AM'), 'N', 70 from dual union all
select 'MQ8', to_date('30-MAY-08 12.20.10 AM','DD-MON-RR HH.MI.SS AM'), 'N', 70 from dual union all
select 'MQ9', to_date('29-MAY-08 11.44.10 AM','DD-MON-RR HH.MI.SS AM'), '', 80 from dual union all
select 'MQ1', to_date('29-MAY-08 11.57.59 AM','DD-MON-RR HH.MI.SS AM'), '', null from dual union all
select 'MQ2', to_date('29-MAY-08 12.00.24 PM','DD-MON-RR HH.MI.SS AM'), '', null from dual union all
select 'MQ8', to_date('12-MAY-08 12.20.10 AM','DD-MON-RR HH.MI.SS AM'), 'N', 70 from dual union all
select 'MQ8', to_date('10-MAY-08 02.20.10 AM','DD-MON-RR HH.MI.SS AM'), 'N', 70 from dual union all
select 'MQ5', to_date('30-MAY-08 12.19.56 AM','DD-MON-RR HH.MI.SS AM'), 'N', 11 from dual union all
select 'MQ7', to_date('30-MAY-08 12.20.04 AM','DD-MON-RR HH.MI.SS AM'), 'N', 11 from dual union all
select 'MQ7', to_date('30-MAY-08 12.20.04 AM','DD-MON-RR HH.MI.SS AM'), 'N', 11 from dual union all
select 'MQ7', to_date('20-MAY-08 12.20.04 AM','DD-MON-RR HH.MI.SS AM'), 'N', 11 from dual union all
select 'MQ7', to_date('29-MAY-08 12.20.04 AM','DD-MON-RR HH.MI.SS AM'), 'N', 11 from dual union all
select 'MQ9', to_date('29-MAY-08 11.57.59 AM','DD-MON-RR HH.MI.SS AM'), 'N', 11 from dual union all
select 'MQ10', to_date('29-MAY-08 12.00.24 PM','DD-MON-RR HH.MI.SS AM'), 'Y', 80 from dual union all
select 'MQ6', to_date('30-MAY-08 12.19.56 AM','DD-MON-RR HH.MI.SS AM'), 'Y', 10 from dual union all
select 'MQ7', to_date('30-MAY-08 12.20.04 AM','DD-MON-RR HH.MI.SS AM'), 'Y', 10 from dual union all
select 'MQ4', to_date('30-MAY-08 12.20.10 AM','DD-MON-RR HH.MI.SS AM'), 'N', null from dual)
--
select
application,
null as subapp,
'LOCATION'||
row_number() over (partition by application,subapp,queuecd order by application,subapp,queuecd,location)||
'('||location||')' as location,
queuecd,
'Queue'||replace(queuecd,'MQ') as queue,
total
from (
select
a.application,
a.subapp,
b.location_id as location,
b.code as queuecd,
count(*) as total
from (
select
connect_by_root code as application,
prior_field as subapp,
code as ccode,
level as lvl
from table_a
start with prior_field = 'ROOT'
connect by prior_field = prior code) a,
table_b b
where a.ccode = b.code
and a.lvl = 2
group by a.application, a.subapp, b.location_id, b.code
)
order by application, subapp, to_number(replace(queuecd,'MQ')), location;
APPLICATION SUBAPP LOCATION QUEUECD QUEUE TOTAL
------------ -------- --------------- -------- --------- ----------
ZAPP1 LOCATION1(11) MQ9 Queue9 1
ZAPP1 LOCATION2(80) MQ9 Queue9 1
ZAPP1 LOCATION1(80) MQ10 Queue10 1
SQL>
SQL>
SQL>
SQL>
HTH
prtz
|
|
|
Goto Forum:
Current Time: Wed Feb 12 18:49:19 CST 2025
|