IBM Mainframe Forum Index
 
Log In
 
IBM Mainframe Forum Index Mainframe: Search IBM Mainframe Forum: FAQ Register
 

Problem in recursive SQL


IBM Mainframe Forums -> DB2
Post new topic   Reply to topic
View previous topic :: View next topic  
Author Message
Learncoholic

New User


Joined: 20 Sep 2007
Posts: 97
Location: India

PostPosted: Wed Nov 21, 2012 8:59 pm
Reply with quote

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
View user's profile Send private message
GauravKudesiya
Warnings : 1

New User


Joined: 11 Oct 2008
Posts: 74
Location: Chicago, IL

PostPosted: Wed Nov 21, 2012 9:56 pm
Reply with quote

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
View user's profile Send private message
Learncoholic

New User


Joined: 20 Sep 2007
Posts: 97
Location: India

PostPosted: Wed Nov 21, 2012 10:00 pm
Reply with quote

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
View user's profile Send private message
Nic Clouston

Global Moderator


Joined: 10 May 2007
Posts: 2455
Location: Hampshire, UK

PostPosted: Thu Nov 22, 2012 12:49 am
Reply with quote

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
View user's profile Send private message
gylbharat

Active Member


Joined: 31 Jul 2009
Posts: 565
Location: Bangalore

PostPosted: Thu Nov 22, 2012 10:21 am
Reply with quote

you may try looking in using XMLAGG function
Back to top
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1281
Location: Belgium

PostPosted: Thu Nov 22, 2012 4:52 pm
Reply with quote

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
View user's profile Send private message
Learncoholic

New User


Joined: 20 Sep 2007
Posts: 97
Location: India

PostPosted: Fri Nov 23, 2012 7:01 pm
Reply with quote

Hi Guy,

Thanks a ton!!!
This worked for me.

Thanks
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic View Bookmarks
All times are GMT + 6 Hours
Forum Index -> DB2

 


Similar Topics
Topic Forum Replies
No new posts Map Vols and Problem Dataset All Other Mainframe Topics 2
No new posts z/vm installation problem All Other Mainframe Topics 0
No new posts Job scheduling problem. JCL & VSAM 9
No new posts Problem with IFTHEN=(WHEN=GROUP,BEGIN... DFSORT/ICETOOL 5
No new posts Need to add field to copybook, proble... COBOL Programming 14
Search our Forums:

Back to Top