|
View previous topic :: View next topic
|
| Author |
Message |
r arunmoezhi
New User
Joined: 26 Jun 2009
Posts: 32
Location: Chennai
|
|
|
|
I have a select query as follows:
| Code: |
SELECT COL1,COL2 FROM TABLE
WHERE COL1='X'
|
The result of this query will be:
| Code: |
COL1 COL2
X GI
X GU
X LN
|
My requirement is the query output should be displayed as
| Code: |
COL1 COL2
X GI GU LN
|
Can this formatting be done in the query itself? |
|
| Back to top |
|
 |
karisurya
New User
Joined: 02 Aug 2007
Posts: 64
Location: Bangalore
|
|
|
|
Hi Arun,
I am not sure can it be done or not but just curious why you want to do that?
Is it a exercise or some project requirement? A bit more back ground and clear explanation why you want to do that will help you to get quick replies.
Regards,
Surya |
|
| Back to top |
|
|
r arunmoezhi
New User
Joined: 26 Jun 2009
Posts: 32
Location: Chennai
|
|
|
|
It is a project requirement.
The current query returns multiple records. But if this query is changed so that it returns only one record i can use that record to insert into another table which has only two columns. And the primary key for the second table is col1. So it cannot have duplicates. |
|
| Back to top |
|
|
Srihari Gonugunta
Active User
Joined: 14 Sep 2007
Posts: 295
Location: Singapore
|
|
|
|
Arun,
You can try this. This is tested one. Hope it works for you.
| Code: |
WITH TRANCTE(COL1,COL2,AUX) AS
(SELECT COL1,CAST(COL2 AS VARCHAR(255)),CAST(NULL AS VARCHAR(255))
FROM YOUR_TABLE WHERE COL1 = 'X'
UNION ALL
SELECT A.COL1,A.COL2||' '||B.COL2,B.COL2
FROM TRANCTE A , YOUR_TABLE B
WHERE A.COL1 = B.COL1 AND
LOCATE(B.COL2,A.COL2) = 0 AND
COALESCE(A.AUX,' ') < B.COL2
)
SELECT TX.COL1, TX.COL2
FROM TRANCTE AS TX
WHERE LENGTH(TX.COL2) = (SELECT MAX(LENGTH(T.COL2))
FROM TRANCTE AS T
WHERE T.COL1 = TX.COL1)
FETCH FIRST 1 ROWS ONLY; |
|
|
| Back to top |
|
|
r arunmoezhi
New User
Joined: 26 Jun 2009
Posts: 32
Location: Chennai
|
|
|
|
worked perfectly.  thanks a lot.
can you please post an explanation of this query. especially with respect to LOCATE. |
|
| Back to top |
|
|
karisurya
New User
Joined: 02 Aug 2007
Posts: 64
Location: Bangalore
|
|
|
|
Hi Srihari,
Really good info and working perfect for me when tested.
I am realtively new to Recursive sql can you please explain the query or please guide me where i can find material on this.
Regards,
Surya |
|
| Back to top |
|
|
Srihari Gonugunta
Active User
Joined: 14 Sep 2007
Posts: 295
Location: Singapore
|
|
|
|
Hi,
Please find the detailed explanation of recursive SQL in the following link.
publibz.boulder.ibm.com/cgi-bin/bookmgr_OS390/BOOKS/DSNAPJ10/APPENDIX1.5?DT=20040210163115#HDRQAPPENZ
I used LOCATE function to avoid concatenation of duplicate values.
| Code: |
WITH TRANCTE(COL1,COL2,AUX) AS
(SELECT COL1,CAST(COL2 AS VARCHAR(255)),CAST(NULL AS VARCHAR(255))
FROM YOUR_TABLE WHERE COL1 = 'X'
UNION ALL
SELECT A.COL1,A.COL2||' '||B.COL2,B.COL2
FROM TRANCTE A , YOUR_TABLE B
WHERE A.COL1 = B.COL1 AND
LOCATE(B.COL2,A.COL2) = 0 AND
COALESCE(A.AUX,' ') < B.COL2
)
SELECT * FROM TRANCTE; |
Check this out for all the rows generated by the above query. That gives better understanding. |
|
| Back to top |
|
|
karisurya
New User
Joined: 02 Aug 2007
Posts: 64
Location: Bangalore
|
|
|
|
Hi Srihari,
Thank you for the info.
Regards,
Surya |
|
| Back to top |
|
|
|
|
