View previous topic :: View next topic
|
Author |
Message |
Learncoholic
New User
Joined: 20 Sep 2007 Posts: 97 Location: India
|
|
|
|
Hi,
I have a table TABLE1 containing 2 columns names CO & LOB_DATA.
There are 4 values in the table.
CO LOB_DATA
00 AAAAAA
00 BBBBBB
00 CCCCCC
00 DDDDDD
Now, I need to concatenate all LOB under '00'.
I have used the below SQL:
Code: |
WITH VAL_DATA (CO_ID,LOB) AS
(SELECT CO,CAST(MAX(LOB_DATA) AS VARCHAR(100))
FROM Creator.TABLE1
WHERE CO = '00'
GROUP BY CO
UNION ALL
SELECT A.CO,A.LOB_DATA||';'||B.LOB
FROM Creator.TABLE1 A,VAL_DATA B
WHERE A.CO = B.CO_ID
AND A.CO = '00'
AND A.LOB_DATA < B.LOB
)
SELECT * FROM VAL_DATA; |
However, this is going into infinite loop.
May I request your help in this regard.
Thanks |
|
Back to top |
|
|
GauravKudesiya Warnings : 1 New User
Joined: 11 Oct 2008 Posts: 74 Location: Chicago, IL
|
|
|
|
Quote: |
Now, I need to concatenate all LOB under '00'. |
Do you need Output as
'AAAAAABBBBBBCCCCCCDDDDDD'
If yes, then why you are trying to do this with Query, you can fatch the result fo simple select Query and String the LOB_DATA data untill SQLCODE is 100. |
|
Back to top |
|
|
Learncoholic
New User
Joined: 20 Sep 2007 Posts: 97 Location: India
|
|
|
|
Hey GauravKudesiya,
You are correct about my requirement.
We may use a program to selectively fetch each row & concatenate it.
But I wish to have it done using native/recursive SQL.
Thanks |
|
Back to top |
|
|
Nic Clouston
Global Moderator
Joined: 10 May 2007 Posts: 2455 Location: Hampshire, UK
|
|
|
|
Quote: |
But I wish to have it done using native/recursive SQL |
Why? Keep it simple. It is more understandable/maintainable and usually more efficient - certainly less DB2 resource required and that is the expensive part of your process. |
|
Back to top |
|
|
gylbharat
Active Member
Joined: 31 Jul 2009 Posts: 565 Location: Bangalore
|
|
|
|
you may try looking in using XMLAGG function |
|
Back to top |
|
|
GuyC
Senior Member
Joined: 11 Aug 2009 Posts: 1281 Location: Belgium
|
|
|
|
it is always advised that when using recursive SQL you use a counter to avoid infinite loops :
Code: |
WITH VAL_DATA (iteration,CO_ID,LOB) AS
(SELECT 1, CO,CAST(MAX(LOB_DATA) AS VARCHAR(100))
FROM Creator.TABLE1
WHERE CO = '00'
GROUP BY CO
UNION ALL
SELECT b.iteration + 1, A.CO,A.LOB_DATA||';'||B.LOB
FROM Creator.TABLE1 A,VAL_DATA B
WHERE A.CO = B.CO_ID
AND A.CO = '00'
AND A.LOB_DATA < B.LOB
and b.iteration < 5
)
SELECT * FROM VAL_DATA; |
from the result you might figure out what's wrong :
1) It goes in a loop because 'CCCCCC' < 'CCCCCC;DDDDDD'.
if you use min() instead of max() you would avoid this pitfall because 'CCCCCC' > 'CCCCCC;DDDDDD' is not true.
2) you join too much rows per iteration
in iteration 2 you join DDD with AAA, BBB and CCC. you should only join with CCC.
in this case you should use
Code: |
and a.lob_data = (select max(a2.lob_data) from table1 a2
WHERE A2.CO = b.CO
AND A2.CO = '00'
AND A2.LOB_DATA < b.LOB_data ) |
or min() if you followed the instruction in 1) |
|
Back to top |
|
|
Learncoholic
New User
Joined: 20 Sep 2007 Posts: 97 Location: India
|
|
|
|
Hi Guy,
Thanks a ton!!!
This worked for me.
Thanks |
|
Back to top |
|
|
|