Home » SQL & PL/SQL » SQL & PL/SQL » help to modify query (merged, merged, merged)
help to modify query (merged, merged, merged) [message #337003] |
Tue, 29 July 2008 11:09  |
Olivia
Messages: 519 Registered: June 2008
|
Senior Member |
|
|
SELECT
B.LINK_CODE,------------ giving application/sub application code
A.LOC_CD,
C.LOC ,
B.MIXED_CODE AS Q_CODE,---queue codes
B.DSC AS Q, ------giving queue names
COUNT(*) AS "Total Count"
FROM Q_PROCESS A,
Q_TBL B,
LOCATION C
WHERE A.LOC_CD = C.LOC_CD
AND A.Q_NAME = B.MIXED_CODE ------queue code
AND B.LINK_CODE IN
( SELECT MIXED_CODE
FROM Q_TBL
WHERE LINK_CODE='AIX')
/* AND ( param_q_cd IS NULL OR ( param_q_cd IS NOT NULL AND B.MIXED_CODE = param_q_cd )) */
GROUP BY B.LINK_CODE,
A.LOC_CD,
C.LOC,
B.MIXED_CODE,
B.DSC
ORDER BY B.LINK_CODE ;
Problem here is that for a particular application (LINK_CODE) application description is there in DSC
In DSC column all the description for the application, sub application and queue's are there.
I want to display the Application name instead of Application code.
Its not possible for me to change table structure. Need your help!
Regards,
Oli
[Updated on: Tue, 29 July 2008 11:13] Report message to a moderator
|
|
|
|
Re: How to write (modify) the query for this? [message #337006 is a reply to message #337005] |
Tue, 29 July 2008 11:16   |
Olivia
Messages: 519 Registered: June 2008
|
Senior Member |
|
|
Sorry!! Formatted!
SELECT
B.LINK_CODE,------------ giving application/sub application code
A.LOC_CD,
C.LOC ,
B.MIXED_CODE AS Q_CODE,---queue codes
B.DSC AS Q, ------giving queue names
COUNT(*) AS "Total Count"
FROM Q_PROCESS A,
Q_TBL B,
LOCATION C
WHERE A.LOC_CD = C.LOC_CD
AND A.Q_NAME = B.MIXED_CODE ------queue code
AND B.LINK_CODE IN
( SELECT MIXED_CODE
FROM Q_TBL
WHERE LINK_CODE='AIX')
/* AND ( param_q_cd IS NULL OR ( param_q_cd IS NOT NULL AND B.MIXED_CODE = param_q_cd )) */
GROUP BY B.LINK_CODE,
A.LOC_CD,
C.LOC,
B.MIXED_CODE,
B.DSC
ORDER BY B.LINK_CODE ;
Problem here is that for a particular application (LINK_CODE) application description is there in DSC
In DSC column all the description for the application, sub application and queue's are there.
I want to display the Application name instead of Application code.
Its not possible for me to change table structure. Need your help!
Regards,
Oli
|
|
|
|
Re: How to write (modify) the query for this? [message #337010 is a reply to message #337006] |
Tue, 29 July 2008 11:28   |
flyboy
Messages: 1903 Registered: November 2006
|
Senior Member |
|
|
Olivia wrote on Tue, 29 July 2008 18:16 | SELECT
...
B.DSC AS Q, ------giving queue names
COUNT(*) AS "Total Count"
FROM Q_PROCESS A,
Q_TBL B,
LOCATION C
WHERE ...
...
In DSC column all the description for the application, sub application and queue's are there.
...
|
Are you not contradicting yourself?
What is the exact format of DSC (with example)?
Why would you need some part of DSC if you already show it whole (if your second description in words is true)?
|
|
|
Re: How to write (modify) the query for this? [message #337151 is a reply to message #337010] |
Wed, 30 July 2008 01:17   |
Olivia
Messages: 519 Registered: June 2008
|
Senior Member |
|
|
Quote: |
Are you not contradicting yourself?
|
In DSC column may gave application name, sub application name or queue name against a code ( application code) in LINK_CODE.
DSC
_____
AIX APPLICATION
QUEUE1
QUEUE2
AJX APPLICATION
QUEUE3
QUEUE4
Output: I expecting application name ( instead of application code which is in LINK_CODE).. and also Queue Name
SELECT
B.LINK_CODE,( Instead of code I want to display description which value is in B.DSC)------------ giving application/sub application code
A.LOC_CD,
C.LOC ,
B.MIXED_CODE AS Q_CODE,---queue codes
B.DSC AS Q, ------giving queue names
COUNT(*) AS "Total Count"
....
[Updated on: Wed, 30 July 2008 01:19] Report message to a moderator
|
|
|
Re: How to write (modify) the query for this? [message #337172 is a reply to message #337003] |
Wed, 30 July 2008 01:45   |
flyboy
Messages: 1903 Registered: November 2006
|
Senior Member |
|
|
Quote: | DSC
_____
AIX APPLICATION
QUEUE1
QUEUE2
AJX APPLICATION
QUEUE3
QUEUE4
|
Is this sample output(?) showing 6 different rows, 2 rows or 1 row?
If 6 rows, what to show, if DSC = QUEUE3?
If all cases, do you not mind that "queue names" may/will contain application code as well.
Keep in mind that nobody in this forum knows your table structure, their relationship and sample data with its meaning.
We know only the facts you provided; do you think that it describes the situation without any need to guess?
To clear any doubts, you should follow anacedent's advice: Quote: | # Provide DDL (CREATE TABLE, etc.) as appropriate instead of listing the table structure. It'll help people setting up a test set (yes, some people really do that)
# Provide INSERT statements for sample data instead of pasting in or mocking up the results of a SELECT statement.
# Provide your expected result set and explain the rules/reasons that lead to it.
|
|
|
|
Re: How to write (modify) the query for this? [message #337186 is a reply to message #337172] |
Wed, 30 July 2008 02:10   |
Olivia
Messages: 519 Registered: June 2008
|
Senior Member |
|
|
Yah, I do understand Flyboy
But Its not possible for me to send the DDL, INSERT statement.
Table structure for Q_table is like below:
MIXED_CODE DSC LINK_CODE
---- ---------- ---- -------------------------------------------------- ---- ---------------------
AIX1 Application AIX1 ROOT
XSA1 Sub application1(AIX) AIX1
XSA2 Sub application2(AIX) AIX1
XSA3 Sub application3(AIX) AIX1
XSA4 Sub application4(AIX) AIX1
XSA5 Sub application5(AIX) AIX1
YAP1 ApplicationY ROOT
YSA1 Sub application1(Y) YAP1
YSA2 Sub application2(Y) YAP1
YSA3 Sub application3(Y) YAP1
YSA4 Sub application4(Y) YAP1
ZAP1 ApplicationZ (No sub appl) ROOT
MQ1 Queue1 YSA1
MQ2 Queue2 YSA1
MQ3 Queue3 YSA1
MQ4 Queue4 YSA1
MQ5 Queue5 YSA1
MQ6 Queue6 XSA1
MQ7 Queue7 XSA1
MQ8 Queue8 XSA2
MQ9 Queue9 XSA3
MQ10 Queue10 XSA4
MQ9 Queue9 ZAP1
MQ10 Queue10 ZAP1
Q_PROCESS
_______________
Q_NAME
LOC_CD
In the output,
for the query,
SELECT
B.LINK_CODE,------------ giving application/sub application code
A.LOC_CD,
C.LOC ,
B.MIXED_CODE AS Q_CODE,---queue codes
B.DSC AS Q, ------giving queue names
COUNT(*) AS "Total Count"
FROM Q_PROCESS A,
Q_TBL B,
LOCATION C
WHERE A.LOC_CD = C.LOC_CD
AND A.Q_NAME = B.MIXED_CODE ------queue code
AND B.LINK_CODE IN
..
Output:
B.LINK_CODE ----Giving application code/sub application code for the associated queue's
B.MIXED_CODE AS Q_CODE--- Giving queue code
B.DSC AS Q --Giving queue names for the queue code
What I want is that I want to display Application name/sub application name( whose description is in DSC) for the Application/sub application code
SELECT
B.LINK_CODE,( * Replace this to get b.dsc for the link_code value)
A.LOC_CD,
C.LOC ,
B.MIXED_CODE AS Q_CODE,---queue codes
B.DSC AS Q, ------giving queue names
COUNT(*) AS "Total Count"
..
B.LINK_CODE,( * Need to Replace this to get b.dsc value because description is there for the link_code value). How I would modify the query?
Thanks to you all people for giving your time.
Its not always possible to share DDL/Insert Statement and the code
Hope you understand!
[Updated on: Wed, 30 July 2008 02:17] Report message to a moderator
|
|
|
Re: How to write (modify) the query for this? [message #337222 is a reply to message #337003] |
Wed, 30 July 2008 03:26   |
flyboy
Messages: 1903 Registered: November 2006
|
Senior Member |
|
|
OK, this is at least better; however as you did not provide the expected result, I am still in doubt what "application code/sub application code for the associated queue's" precisely means.
Is it DSC from row with MIXED_CODE = result_row.LINK_CODE?
Or DSC from the row linked further with the same condition, which has LINK_CODE = 'ROOT'?
Or is it the concatenation of DSC's of all rows on that path?
So, for Queue1, shall it be:
Sub application1(Y)
ApplicationY
ApplicationY/Sub application1(Y)
or something else?
MIXED_CODE shall be unique in all cases.
In the first case, you may simply self-join Q_TABLE on the condition stated before.
In other cases, rewriting it to hierarchical query shall give desired results.
> But Its not possible for me to send the DDL, INSERT statement.
You may post a testcase instead of the real DDL/INSERTs (keep in mind that it shall reproduce the problem too).
|
|
|
Re: How to write (modify) the query for this? [message #337237 is a reply to message #337222] |
Wed, 30 July 2008 04:23   |
Olivia
Messages: 519 Registered: June 2008
|
Senior Member |
|
|
An application may have or may not have sub applications
We need to get the count of queues in a particular application
If an application have subapplication, then need to show sub application wise count.
MIXED_CODE Contains Application code,Sub application code and Queue code
LINK_CODE contains Application Code and Sub application Code
DSC contains description of MIXED_CODE values (i.e, code description)
I have already written the procedure as per the previous requirement.
i.,e
Conside below q_table:
MIXED_CODE DSC LINK_CODE
---- ---------- ---- -------------------------------------------------- ---- ---------------------
AIX1 Application AIX1 ROOT
XSA1 Sub application1(AIX) AIX1
XSA2 Sub application2(AIX) AIX1
XSA3 Sub application3(AIX) AIX1
XSA4 Sub application4(AIX) AIX1
XSA5 Sub application5(AIX) AIX1
MQU1 QUEUE1 XSA1
MQU2 QUEUE 2 XSA2
MQU3 QUEUE 3 XSA1
Q_PROCESS
_______________
Q_NAME
LOC_CD
...
Q_NAME LOC_CD
______ ___
MQU1 10
MQU2 10
MQU2 10
Output expected was:
APPLICATION/SUBAPPLICATION CODE (LINK_CODE) .. QUEUE CODE(MIXED_CODE) QUEUE NAME (Using DSC Column) .. CNT
XSA1 MQU1 QUEUE1 1
XSA2 MQU2 QUEUE2 2
...
Now the requirement has been changed
Instead of Application code , application name is being expected ...which is in DSC.
SELECT
B.LINK_CODE,( * Need to Replace this with b.dsc value for the link_code value)
A.LOC_CD,
C.LOC ,
B.MIXED_CODE AS Q_CODE,---queue codes
B.DSC AS Q, ------giving queue names
COUNT(*) AS "Total Count"
..
Regards,
Oli
|
|
|
Re: How to write (modify) the query for this? [message #337239 is a reply to message #337222] |
Wed, 30 July 2008 04:51   |
flyboy
Messages: 1903 Registered: November 2006
|
Senior Member |
|
|
flyboy wrote on Wed, 30 July 2008 10:26 | however as you did not provide the expected result, I am still in doubt what "application code/sub application code for the associated queue's" precisely means.
|
As you provided two rows from the current output, is it so hard to post the expected two rows too?
|
|
|
Re: How to write (modify) the query for this? [message #337243 is a reply to message #337239] |
Wed, 30 July 2008 05:01   |
Olivia
Messages: 519 Registered: June 2008
|
Senior Member |
|
|
SELECT
[COLOR=red][B]B.LINK_CODE[/B][/COLOR],
A.LOC_CD,
C.LOC AS LOCATION,
B.MIXED_CODE AS "QUEUE CODE",
B.DSC AS QUEUE,
COUNT(*) AS CNT
FROM Q_PROCESS A,
Q_TBL B,
LOCATION C
WHERE A.LOC_CD = C.LOC_CD(+)
AND A.Q_NAME = B.MIXED_CODE
AND B.LINK_CODE IN
( SELECT MIXED_CODE
FROM Q_TBL
WHERE LINK_CODE='AIX1')
GROUP BY B.DSC,
B.LINK_CODE,
A.LOC_CD,
C.LOC,
B.MIXED_CODE,
B.DSC
OUTPUT:
LINK LO LOCATION QUEU QUEUE CNT
---- -- ------------------- ---- -------------------------------------------------- ----------
XSA1 31 New York MQU1 QUEUE 1 2
XSA2 99 Albama MQU1 QUEUE 1 2
XSA2 31 New York MQU5 QUEUE 3 4
XSA3 31 New York MQU7 QUEUE 4 1
Requirement:
Want to display description from Q_TBL instead of code value in 1st column (description is in DSC column)
In Q_TBL,
DSC having description value.
MIXED_CODE -- APPLICATION/SUB APPLICATION/QUEUE CODE
LINK_CDE -- APPLICATION/SUBAPPLICATION CODE
[Updated on: Wed, 30 July 2008 05:08] Report message to a moderator
|
|
|
|
|
|
Re: How to write (modify) the query for this? [message #337277 is a reply to message #337249] |
Wed, 30 July 2008 07:20   |
flyboy
Messages: 1903 Registered: November 2006
|
Senior Member |
|
|
Olivia wrote on Wed, 30 July 2008 12:11 | there is no primary column defined.
| I do not know, what you mean by this; but from the sample data it seems that MIXED_CODE column is at least unique. If not, you have logical problem - one queue may belong to more (sub)applications, or the link between (sub)application and queues is different than the one I deduced (as you did not explicitly state in your post).
Have a look at the result of this query:
SELECT b.dsc, b.mixed_code, b.link_code, d.dsc
FROM q_tbl b, q_tbl d
WHERE b.link_code = d.mixed_code Maybe you want the (second) DSC; but it may not be so as your posts are still not clear.
|
|
|
Re: How to write (modify) the query for this? [message #337309 is a reply to message #337277] |
Wed, 30 July 2008 09:05   |
Olivia
Messages: 519 Registered: June 2008
|
Senior Member |
|
|
TABLE STRUCTURE WITH TEST DATA:
TABLE: TEST_00001
_____________________
SQL> DESC TEST_00001
Name
----------------------------------------------------------
HDR
MIXED_CODE
LINK_CODE
DSC
SQL> SELECT * FROM TEST_00001;
HDR MIXE LINK DSC
---- ---- ---- --------------------------------------------------
MQNM MQU1 XSA1 Queue 1
MQNM MQU2 XSA1 Queue 2
MQNM MQU3 XSA1 Queue 3
MQNM MQU4 XSA2 Queue 4
MQNM MQU5 XSA2 Queue 5
MQNM MQU6 XSA1 Queue 6
SAPP XSA2 AIX1 Sub application2(AIX)
SAPP XSA1 AIX1 Sub application1(AIX)
8 rows selected.
TABLE: TEST_00003
SQL> DESC TEST_00003
Name
--------------------------------------------------------------------------------------------------
Q_NAME
LOC_CD
UPD_DT
SQL> SELECT * FROM TEST_00003;
Q_NAME LO UPD_DT
------------------------------ -- ---------------------------
MQU4 31 14-JAN-08 07.33.37 PM
MQU4 31 01-FEB-07 03.44.03 PM
MQU4 31 01-FEB-07 03.44.03 PM
MQU4 31 14-JAN-08 07.33.37 PM
MQU4 31 14-JAN-08 07.33.37 PM
MQU4 31 14-JAN-08 07.33.37 PM
MQU4 31 14-JAN-08 07.33.37 PM
MQU5 31 08-MAY-07 05.40.54 AM
MQU5 31 31-JAN-07 08.34.05 AM
MQU6 03 25-JUL-08 04.47.22 PM
MQU6 03 25-JUL-08 04.47.22 PM
Q_NAME LO UPD_DT
------------------------------ -- ---------------------------
MQU6 03 25-JUL-08 04.47.22 PM
MQU6 03 25-JUL-08 04.47.22 PM
MQU6 03 25-JUL-08 04.47.21 PM
MQU6 03 25-JUL-08 04.47.21 PM
MQU6 03 25-JUL-08 04.47.21 PM
MQU6 03 25-JUL-08 04.47.21 PM
MQU6 03 25-JUL-08 04.47.22 PM
MQU6 03 25-JUL-08 04.47.22 PM
MQU6 03 25-JUL-08 04.47.22 PM
MQU6 03 25-JUL-08 04.47.22 PM
MQU6 03 25-JUL-08 04.47.22 PM
Q_NAME LO UPD_DT
------------------------------ -- ---------------------------
MQU6 03 25-JUL-08 04.47.22 PM
MQU6 03 25-JUL-08 04.47.22 PM
MQU6 03 25-JUL-08 04.47.22 PM
MQU2 18 30-JUL-08 09.21.36 AM
MQU2 18 30-JUL-08 09.21.44 AM
MQU1 18 30-JUL-08 09.21.55 AM
MQU3 18 30-JUL-08 09.22.04 AM
MQU2 99 30-JUL-08 09.22.22 AM
MQU1 99 30-JUL-08 09.22.30 AM
MQU3 31 30-JUL-08 09.22.36 AM
MQU3 18 30-JUL-08 09.22.43 AM
Q_NAME LO UPD_DT
------------------------------ -- ---------------------------
MQU2 18 30-JUL-08 09.22.49 AM
MQU2 99 30-JUL-08 09.22.55 AM
MQU1 99 30-JUL-08 09.23.15 AM
MQU3 31 30-JUL-08 09.23.26 AM
37 rows selected.
TABLE: LOC_0001
________________
LO LOC
-- --------------------
99 Ohio
18 Albama
31 New York
03 California
Here is the procedure below that I have written to get the count for all sub application for an application.
In the output, in first column I want to display the sub application name instead of code.eg, instead of
XSA1 it should display Sub application1(AIX) etc...
The above table will contains huge amount of data.There may be many applications.Below procedure is just part
of one criteria.There may be so many criteria like application may not have sub applications..
In the output, in first column I want to display the sub application name instead of code.eg, instead of
XSA1 it should display Sub application1(AIX) etc...
I am not being able to figure out how to do that. Need your help!
CREATE OR REPLACE PROCEDURE YGET_QUEUE_DTL
( in_app_code IN TEST_00001.LINK_CODE%TYPE,
in_q_code IN TEST_00001.MIXED_CODE%TYPE DEFAULT NULL,
RF1 OUT globalPkg.RCT1)
IS
BEGIN
OPEN RF1 FOR
SELECT B.LINK_CODE "APP CODE",
A.LOC_CD "LOCATION CODE",
C.LOC AS "LOC NAME",
B.MIXED_CODE AS "QUEUE CODE",
B.DSC AS "QUEUE NAME",
COUNT(*) AS TOTAL
FROM TEST_00003 A,
TEST_00001 B,
LOC_0001 C
WHERE A.LOC_CD = C.LOC_CD
AND SUBSTR(A.Q_NAME,1,4) = B.MIXED_CODE
AND B.LINK_CODE IN
( SELECT MIXED_CODE
FROM TEST_00001
WHERE LINK_CODE=in_app_code)
AND ( in_q_code IS NULL OR ( in_q_code IS NOT NULL AND B.MIXED_CODE = in_q_code ))
GROUP BY B.LINK_CODE,
A.LOC_CD,
C.LOC,
B.MIXED_CODE,
B.DSC
ORDER BY B.LINK_CODE ;
END;
/
OUTPUT:
____________
Procedure created.
SQL> VARIABLE CNT REFCURSOR
SQL> EXEC YGET_QUEUE_DTL('AIX1',NULL,:CNT)
PL/SQL procedure successfully completed.
SQL> PRINT CNT
APP LO LOC NAME QUEU QUEUE NAME TOTAL
---- -- -------------------- ---- -------------------------------------------------- ----------
XSA1 03 California MQU6 Queue 6 16
XSA1 18 Albama MQU1 Queue 1 1
XSA1 18 Albama MQU2 Queue 2 3
XSA1 18 Albama MQU3 Queue 3 2
XSA1 31 New York MQU3 Queue 3 2
XSA1 99 Ohio MQU1 Queue 1 2
XSA1 99 Ohio MQU2 Queue 2 2
XSA2 31 New York MQU4 Queue 4 7
XSA2 31 New York MQU5 Queue 5 2
9 rows selected.
Hope that it would help you understand my problem!
Regards,
Oli
|
|
|
Re: How to write (modify) the query for this? [message #337321 is a reply to message #337003] |
Wed, 30 July 2008 10:03   |
Olivia
Messages: 519 Registered: June 2008
|
Senior Member |
|
|
Hi , I am here with providing the information required. Hope that
now it would be clear to you what i am expecting.
SCRIPT:
-------------
CREATE TABLE TEST_000001
( HDR VARCHAR2(4),
MIXED_CODE VARCHAR2(4),
LINK_CODE VARCHAR2(4),
DSC VARCHAR2(30));
INSERT INTO TEST_000001 VALUES('MQNM','MQU1','XSA1','Queue 1');
INSERT INTO TEST_000001 VALUES('MQNM','MQU2','XSA1','Queue 2');
INSERT INTO TEST_000001 VALUES('MQNM','MQU3','XSA1','Queue 3');
INSERT INTO TEST_000001 VALUES('MQNM','MQU4','XSA2','Queue 4');
INSERT INTO TEST_000001 VALUES('MQNM','MQU5','XSA2','Queue 5');
INSERT INTO TEST_000001 VALUES('MQNM','MQU6','XSA1','Queue 6');
INSERT INTO TEST_000001 VALUES('SAPP','XSA2','AIX1','Sub application2(AIX)');
INSERT INTO TEST_000001 VALUES('SAPP','XSA1','AIX1','Sub application1(AIX)');
CREATE TABLE TEST_000003
( Q_NAME VARCHAR2(4),
LOC_CD VARCHAR2(2),
UPD_DT TIMESTAMP);
INSERT INTO TEST_000003 VALUES('MQU1','18',SYSDATE);
INSERT INTO TEST_000003 VALUES('MQU1','18',SYSDATE);
INSERT INTO TEST_000003 VALUES('MQU2','18',SYSDATE);
INSERT INTO TEST_000003 VALUES('MQU3','18',SYSDATE);
INSERT INTO TEST_000003 VALUES('MQU1','18',SYSDATE);
INSERT INTO TEST_000003 VALUES('MQU1','99',SYSDATE);
INSERT INTO TEST_000003 VALUES('MQU2','99',SYSDATE);
INSERT INTO TEST_000003 VALUES('MQU3','31',SYSDATE);
INSERT INTO TEST_000003 VALUES('MQU3','18',SYSDATE);
INSERT INTO TEST_000003 VALUES('MQU1','18',SYSDATE);
INSERT INTO TEST_000003 VALUES('MQU1','99',SYSDATE);
INSERT INTO TEST_000003 VALUES('MQU2','99',SYSDATE);
INSERT INTO TEST_000003 VALUES('MQU3','31',SYSDATE);
INSERT INTO TEST_000003 VALUES('MQU6','03',SYSDATE);
CREATE TABLE LOC_000001
( LOC_CD VARCHAR2(2),
LOC VARCHAR2(30)
);
INSERT INTO LOC_000001 VALUES ('99','Ohio');
INSERT INTO LOC_000001 VALUES ('18','Albama');
INSERT INTO LOC_000001 VALUES ('31','New York');
INSERT INTO LOC_000001 VALUES ('03','California');
Procedure to get total count for all sub applications
-----------------------------------------------------
CREATE OR REPLACE PROCEDURE YGET_QUEUE_DTL
( in_app_code IN TEST_000001.LINK_CODE%TYPE,
in_q_code IN TEST_000001.MIXED_CODE%TYPE DEFAULT NULL,
RF1 OUT globalPkg.RCT1)
IS
BEGIN
OPEN RF1 FOR
SELECT B.LINK_CODE "APP CODE",
A.LOC_CD "LOCATION CODE",
C.LOC AS "LOC NAME",
B.MIXED_CODE AS "QUEUE CODE",
B.DSC AS "QUEUE NAME",
COUNT(*) AS TOTAL
FROM TEST_000003 A,
TEST_000001 B,
LOC_000001 C
WHERE A.LOC_CD = C.LOC_CD
AND SUBSTR(A.Q_NAME,1,4) = B.MIXED_CODE
AND B.LINK_CODE IN
( SELECT MIXED_CODE
FROM TEST_000001
WHERE LINK_CODE=in_app_code)
AND ( in_q_code IS NULL OR ( in_q_code IS NOT NULL AND B.MIXED_CODE = in_q_code ))
GROUP BY B.LINK_CODE,
A.LOC_CD,
C.LOC,
B.MIXED_CODE,
B.DSC
ORDER BY B.LINK_CODE ;
END;
/
OUTPUT:
------------
SQL> VARIABLE CNT REFCURSOR
SQL> EXEC YGET_QUEUE_DTL('AIX1',NULL,:CNT)
PL/SQL procedure successfully completed.
SQL> PRINT CNT
APP LO LOC NAME QUEU QUEUE NAME TOTAL
---- -- ------------------------------ ---- ------------------------------ ----------
XSA1 03 California MQU6 Queue 6 1
XSA1 18 Albama MQU1 Queue 1 4
XSA1 18 Albama MQU2 Queue 2 1
XSA1 18 Albama MQU3 Queue 3 2
XSA1 31 New York MQU3 Queue 3 2
XSA1 99 Ohio MQU1 Queue 1 2
XSA1 99 Ohio MQU2 Queue 2 2
7 rows selected.
Requirement:
------------------
In the 1st column Sub Application name should be displayed instead of code.e.g, instead of
XSA1 it should display Sub application1(AIX) etc...
Regards,
Oli
|
|
|
Need help to modify the quey [message #337476 is a reply to message #337003] |
Thu, 31 July 2008 01:00   |
Olivia
Messages: 519 Registered: June 2008
|
Senior Member |
|
|
SCRIPT:
-------------
CREATE TABLE TEST_000001
( HDR VARCHAR2(4),
MIXED_CODE VARCHAR2(4),
LINK_CODE VARCHAR2(4),
DSC VARCHAR2(30));
INSERT INTO TEST_000001 VALUES('MQNM','MQU1','XSA1','Queue 1');
INSERT INTO TEST_000001 VALUES('MQNM','MQU2','XSA1','Queue 2');
INSERT INTO TEST_000001 VALUES('MQNM','MQU3','XSA1','Queue 3');
INSERT INTO TEST_000001 VALUES('MQNM','MQU4','XSA2','Queue 4');
INSERT INTO TEST_000001 VALUES('MQNM','MQU5','XSA2','Queue 5');
INSERT INTO TEST_000001 VALUES('MQNM','MQU6','XSA1','Queue 6');
INSERT INTO TEST_000001 VALUES('SAPP','XSA2','AIX1','Sub application2(AIX)');
INSERT INTO TEST_000001 VALUES('SAPP','XSA1','AIX1','Sub application1(AIX)');
CREATE TABLE TEST_000003
( Q_NAME VARCHAR2(4),
LOC_CD VARCHAR2(2),
UPD_DT TIMESTAMP);
INSERT INTO TEST_000003 VALUES('MQU1','18',SYSDATE);
INSERT INTO TEST_000003 VALUES('MQU1','18',SYSDATE);
INSERT INTO TEST_000003 VALUES('MQU2','18',SYSDATE);
INSERT INTO TEST_000003 VALUES('MQU3','18',SYSDATE);
INSERT INTO TEST_000003 VALUES('MQU1','18',SYSDATE);
INSERT INTO TEST_000003 VALUES('MQU1','99',SYSDATE);
INSERT INTO TEST_000003 VALUES('MQU2','99',SYSDATE);
INSERT INTO TEST_000003 VALUES('MQU3','31',SYSDATE);
INSERT INTO TEST_000003 VALUES('MQU3','18',SYSDATE);
INSERT INTO TEST_000003 VALUES('MQU1','18',SYSDATE);
INSERT INTO TEST_000003 VALUES('MQU1','99',SYSDATE);
INSERT INTO TEST_000003 VALUES('MQU2','99',SYSDATE);
INSERT INTO TEST_000003 VALUES('MQU3','31',SYSDATE);
INSERT INTO TEST_000003 VALUES('MQU6','03',SYSDATE);
CREATE TABLE LOC_000001
( LOC_CD VARCHAR2(2),
LOC VARCHAR2(30)
);
INSERT INTO LOC_000001 VALUES ('99','Ohio');
INSERT INTO LOC_000001 VALUES ('18','Albama');
INSERT INTO LOC_000001 VALUES ('31','New York');
INSERT INTO LOC_000001 VALUES ('03','California');
Procedure to get total count for all sub applications
-----------------------------------------------------
CREATE OR REPLACE PROCEDURE YGET_QUEUE_DTL
( in_app_code IN TEST_000001.LINK_CODE%TYPE,
in_q_code IN TEST_000001.MIXED_CODE%TYPE DEFAULT NULL,
RF1 OUT globalPkg.RCT1)
IS
BEGIN
OPEN RF1 FOR
SELECT B.LINK_CODE "APP CODE",
A.LOC_CD "LOCATION CODE",
C.LOC AS "LOC NAME",
B.MIXED_CODE AS "QUEUE CODE",
B.DSC AS "QUEUE NAME",
COUNT(*) AS TOTAL
FROM TEST_000003 A,
TEST_000001 B,
LOC_000001 C
WHERE A.LOC_CD = C.LOC_CD
AND SUBSTR(A.Q_NAME,1,4) = B.MIXED_CODE
AND B.LINK_CODE IN
( SELECT MIXED_CODE
FROM TEST_000001
WHERE LINK_CODE=in_app_code)
AND ( in_q_code IS NULL OR ( in_q_code IS NOT NULL AND B.MIXED_CODE = in_q_code ))
GROUP BY B.LINK_CODE,
A.LOC_CD,
C.LOC,
B.MIXED_CODE,
B.DSC
ORDER BY B.LINK_CODE ;
END;
/
OUTPUT:
------------
SQL> VARIABLE CNT REFCURSOR
SQL> EXEC YGET_QUEUE_DTL('AIX1',NULL,:CNT)
PL/SQL procedure successfully completed.
SQL> PRINT CNT
APP LO LOC NAME QUEU QUEUE NAME TOTAL
---- -- ------------------------------ ---- ------------------------------ ----------
XSA1 03 California MQU6 Queue 6 1
XSA1 18 Albama MQU1 Queue 1 4
XSA1 18 Albama MQU2 Queue 2 1
XSA1 18 Albama MQU3 Queue 3 2
XSA1 31 New York MQU3 Queue 3 2
XSA1 99 Ohio MQU1 Queue 1 2
XSA1 99 Ohio MQU2 Queue 2 2
7 rows selected.
Requirement:
------------------
In the 1st column Sub Application name should be displayed instead of code.e.g, instead of
XSA1 it should display Sub application1(AIX) etc...
Is it possible? What changes need to be done in the query?
Sorry, for re posting again as I feel that it might be missed out.
Regards,
Oli
|
|
|
|
Re: Need help to modify the quey [message #337486 is a reply to message #337485] |
Thu, 31 July 2008 01:15   |
Olivia
Messages: 519 Registered: June 2008
|
Senior Member |
|
|
Michel Cadot wrote on Thu, 31 July 2008 01:11 | Keep your lines in 80 characters.
You don't need to indent with 20 characters at each level, 2 or 3 are sufficient.
Regards
Michel
|
Thanks for the reply Michel. Ok, I will try to maintain that in future.
What changes need to be done in the query to meet the above mentioned requirement.Need your help!
[Updated on: Thu, 31 July 2008 01:16] Report message to a moderator
|
|
|
Re: Need help to modify the quey [message #337487 is a reply to message #337486] |
Thu, 31 July 2008 01:22   |
S.Rajaram
Messages: 1027 Registered: October 2006 Location: United Kingdom
|
Senior Member |
|
|
Couple of possibilities.
a) You need a self join or outer join to the same table (TEST_000001) depends on how the data is organised.
b) Scalar Subquery
But since you have not shared the data volume I cannot really comment which will be better/ideal. I will leave that to you.
Regards
Raj
[Updated on: Thu, 31 July 2008 01:24] Report message to a moderator
|
|
|
Re: Need help to modify the quey [message #337490 is a reply to message #337487] |
Thu, 31 July 2008 01:29   |
Olivia
Messages: 519 Registered: June 2008
|
Senior Member |
|
|
Thanks Raj for the quick response.
I am not really being able to figure out how the query need to be modified!
Quote: |
But since you have not shared the data volume I cannot really comment which will be better/ideal. I will leave that to you.
|
TEST_000003 will contain millions of data.While the TEST_000001 table will have less than 200 as of now.
I have alresdy put the test cases above.
Need your help!
Regards,
Oli
[Updated on: Thu, 31 July 2008 01:31] Report message to a moderator
|
|
|
|
|
Re: Need help to modify the quey [message #337496 is a reply to message #337490] |
Thu, 31 July 2008 01:39   |
S.Rajaram
Messages: 1027 Registered: October 2006 Location: United Kingdom
|
Senior Member |
|
|
This is what I meant. As I don't want to give you the actual query I have demonstrated it with an example how to do it with emp table.
SQL> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit Production
PL/SQL Release 11.1.0.6.0 - Production
CORE 11.1.0.6.0 Production
TNS for Linux: Version 11.1.0.6.0 - Production
NLSRTL Version 11.1.0.6.0 - Production
SQL> l
1* select * from emp
SQL> /
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 800 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7566 JONES MANAGER 7839 02-APR-81 2975 20
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7788 SCOTT ANALYST 7566 19-APR-87 3000 20
7839 KING PRESIDENT 17-NOV-81 5000 10
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7876 ADAMS CLERK 7788 23-MAY-87 1100 20
7900 JAMES CLERK 7698 03-DEC-81 950 30
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7934 MILLER CLERK 7782 23-JAN-82 1300 10
14 rows selected.
SQL> select e.*, e1.ename from emp e, emp e1
2 where e.mgr = e1.empno (+);
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ENAME
---------- ---------- --------- ---------- --------- ---------- ---------- ---------- ----------
7902 FORD ANALYST 7566 03-DEC-81 3000 20 JONES
7788 SCOTT ANALYST 7566 19-APR-87 3000 20 JONES
7900 JAMES CLERK 7698 03-DEC-81 950 30 BLAKE
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30 BLAKE
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30 BLAKE
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30 BLAKE
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30 BLAKE
7934 MILLER CLERK 7782 23-JAN-82 1300 10 CLARK
7876 ADAMS CLERK 7788 23-MAY-87 1100 20 SCOTT
7782 CLARK MANAGER 7839 09-JUN-81 2450 10 KING
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30 KING
7566 JONES MANAGER 7839 02-APR-81 2975 20 KING
7369 SMITH CLERK 7902 17-DEC-80 800 20 FORD
7839 KING PRESIDENT 17-NOV-81 5000 10
14 rows selected.
SQL> select * from table(dbms_xplan.display());
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2341341676
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 658 | 7 (15)| 00:00:01 |
|* 1 | HASH JOIN OUTER | | 14 | 658 | 7 (15)| 00:00:01 |
| 2 | TABLE ACCESS FULL| EMP | 14 | 518 | 3 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL| EMP | 14 | 140 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("E"."MGR"="E1"."EMPNO"(+))
15 rows selected.
1* select e.*, (select ename from emp where empno = e.mgr) mgr_name from emp e
SQL> /
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO MGR_NAME
---------- ---------- --------- ---------- --------- ---------- ---------- ---------- ----------
7902 FORD ANALYST 7566 03-DEC-81 3000 20 JONES
7788 SCOTT ANALYST 7566 19-APR-87 3000 20 JONES
7900 JAMES CLERK 7698 03-DEC-81 950 30 BLAKE
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30 BLAKE
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30 BLAKE
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30 BLAKE
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30 BLAKE
7934 MILLER CLERK 7782 23-JAN-82 1300 10 CLARK
7876 ADAMS CLERK 7788 23-MAY-87 1100 20 SCOTT
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30 KING
7566 JONES MANAGER 7839 02-APR-81 2975 20 KING
7782 CLARK MANAGER 7839 09-JUN-81 2450 10 KING
7369 SMITH CLERK 7902 17-DEC-80 800 20 FORD
7839 KING PRESIDENT 17-NOV-81 5000 10
14 rows selected.
SQL> select * from table(dbms_xplan.display());
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 4000517069
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 518 | 3 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 10 | 1 (0)| 00:00:01 |
|* 2 | INDEX UNIQUE SCAN | PK_EMP | 1 | | 0 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL | EMP | 14 | 518 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("EMPNO"=:B1)
15 rows selected.
Hope this helps.
Regards
Raj
|
|
|
|
Re: Need help to modify the quey [message #337505 is a reply to message #337497] |
Thu, 31 July 2008 02:11   |
Olivia
Messages: 519 Registered: June 2008
|
Senior Member |
|
|
CREATE OR REPLACE PROCEDURE YGET_QUEUE_DTL
( in_app_code IN TEST_000001.LINK_CODE%TYPE,
in_q_code IN TEST_000001.MIXED_CODE%TYPE DEFAULT NULL,
RF1 OUT globalPkg.RCT1)
IS
BEGIN
OPEN RF1 FOR
SELECT
D.DSC AS "SUB APPLICATION CODE",
B.LINK_CODE "APP CODE",
A.LOC_CD "LOCATION CODE",
C.LOC AS "LOC NAME",
B.MIXED_CODE AS "QUEUE CODE",
B.DSC AS "QUEUE NAME",
COUNT(*) AS TOTAL
FROM TEST_000003 A,
TEST_000001 B,
LOC_000001 C,
TEST_000001 D
WHERE A.LOC_CD = C.LOC_CD
AND SUBSTR(A.Q_NAME,1,4) = B.MIXED_CODE
AND B.LINK_CODE IN
( SELECT MIXED_CODE
FROM TEST_000001
WHERE LINK_CODE=in_app_code)
AND B.LINK_CODE=D.MIXED_CODE(+)
AND ( in_q_code IS NULL OR ( in_q_code IS NOT NULL AND B.MIXED_CODE = in_q_code ))
GROUP BY D.DSC,
B.LINK_CODE,
A.LOC_CD,
C.LOC,
B.MIXED_CODE,
B.DSC
ORDER BY B.LINK_CODE ;
END;
/
@Raj,Thanks for your help!
|
|
|
|
|
Re: How to write (modify) the query for this? [message #337565 is a reply to message #337003] |
Thu, 31 July 2008 04:41   |
flyboy
Messages: 1903 Registered: November 2006
|
Senior Member |
|
|
Quote: | So, for Queue1, shall it be:
Sub application1(Y)
ApplicationY
ApplicationY/Sub application1(Y)
or something else?
| Perfect. Finally your post contains required output (first variant from the listed ones) and scripts. Here, I am a little puzzled, as they show NO data integrity between applications and queues - e.g. you can easily enter INSERT INTO TEST_000001 VALUES('SAPP','XSA2','AIX1','Sub application3(AIX)'); (two applications with the same MIXED_CODE) or INSERT INTO TEST_000001 VALUES('MQNM','MQU7','AAAA','Queue 7'); (a queue with LINK_CODE not pointing to any existing application).
Anyway, did you try the SELECT statement I posted in my previous post? This shall get required results (have a look at the two last columns).
As it contains the main table alias from the posted SELECT statement, it shall not be problem to include it to posted query.
|
|
|
|
Goto Forum:
Current Time: Wed Feb 19 15:29:16 CST 2025
|