Portal | Manuals | References | Downloads | Info | Programs | JCLs | Mainframe wiki | Quick Ref
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist 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: 88
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: 88
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: 1922
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: 1281
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: 88
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 Having a problem FTP'ng to the MF pahiker All Other Mainframe Topics 2 Fri Nov 10, 2017 8:20 pm
No new posts LMINIT problem - not finding DDNAME Danielle.Filteau CLIST & REXX 7 Tue Sep 19, 2017 9:57 pm
No new posts Problem commiting to DB2 from IMS MPP Ole Soerensen IMS DB/DC 2 Wed Jul 26, 2017 5:19 pm
No new posts Strings with double quotes having pro... raja Arumugam All Other Mainframe Topics 11 Thu Mar 30, 2017 10:34 am
No new posts Problem reading GTF trace output danik56 ABENDS & Debugging 7 Thu Mar 16, 2017 1:02 pm

Facebook
Back to Top
 
Job Vacancies | Forum Rules | Bookmarks | Subscriptions | FAQ | Polls | Contact Us