Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

Problem in recursive SQL

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
Learncoholic

New User


Joined: 20 Sep 2007
Posts: 73
Location: India

PostPosted: Wed Nov 21, 2012 8:59 pm    Post subject: Problem in recursive SQL
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    Post subject: Reply to: Problem in recursive SQL
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: 73
Location: India

PostPosted: Wed Nov 21, 2012 10:00 pm    Post subject: Reply to: Problem in recursive SQL
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: 1715
Location: UK

PostPosted: Thu Nov 22, 2012 12:49 am    Post subject:
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    Post subject:
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: 1278
Location: Belgium

PostPosted: Thu Nov 22, 2012 4:52 pm    Post subject:
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: 73
Location: India

PostPosted: Fri Nov 23, 2012 7:01 pm    Post subject: Reply to: Problem in recursive SQL
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    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts Problem in writing Output file vickey_dw COBOL Programming 5 Mon Nov 14, 2016 11:14 pm
No new posts Problem in Running Query via JCL vickey_dw DB2 3 Tue Oct 18, 2016 11:11 pm
No new posts DB2 Streaming Batch Processing Problem Manshadi DB2 4 Sat Sep 24, 2016 12:14 pm
No new posts Problem with GETMAIN command amitc23 CICS 6 Thu Sep 01, 2016 1:01 pm
No new posts TXT2PDF problem - printing @ in PDF vipul65 All Other Mainframe Topics 9 Thu Aug 25, 2016 12:48 am


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us