Home » SQL & PL/SQL » SQL & PL/SQL » Problem: Need help! (Oracle 9i)
Problem: Need help! [message #331646] Fri, 04 July 2008 05:47 Go to next message
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 Go to previous messageGo to next message
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 Go to previous message
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

Previous Topic: Stripping formatting characters
Next Topic: is there anyway to get the 10g|11g DBMS_CRYPTO stuff for 9i?
Goto Forum:
  


Current Time: Sun Dec 04 18:48:16 CST 2016

Total time taken to generate the page: 0.06513 seconds