Page 1 of 2
Recursive SQL in DB2
Posted:
Mon Nov 14, 2016 9:40 am
by pawarsandip21
Hi,
Can some one help to convert rows into columns using recursive query concept in DB2?
Suppose I have DB2 table India_City with data as below:
City Name State
Mumbai Maharashtra
Pune Maharashtra
Bangalore Karanatka
Hubli Karanatka
Indore MP
Surat Gujrat
By using recursive query, I have to bring output as below:
Maharashtra Karanatka MP Gujrat
Mumbai -- -- --
Pune -- -- --
-- Bangalore -- --
-- Hubli -- --
-- -- Indore --
-- -- -- Surat
Thanks,
Sandip
CODE' d
Re: Recursive SQL in DB2
Posted:
Mon Nov 14, 2016 1:57 pm
by prino
DB2 does not have a recursive query concept! Period. Full stop. End of story.
Re: Recursive SQL in DB2
Posted:
Mon Nov 14, 2016 2:36 pm
by pranav283
@prino,
DB2 does have a recursive SQL concept!
@pawarsandip21
I don't think this is how recursive query works in DB2. You cannot convert a table row to a table column.
But, if you meant 'columns' as 'fields' in a file - then you can do it by using a COBOL table (like an array).
Re: Recursive SQL in DB2
Posted:
Mon Nov 14, 2016 8:48 pm
by pawarsandip21
@prino - DB2 have a recursive query concept... we can use CTE (Common Table Expression) that starts with WITH command.
@pranav283 - I have to implement this in DB2....so I have to design a query for this.
Thanks,
Sandip
Re: Recursive SQL in DB2
Posted:
Mon Nov 14, 2016 9:01 pm
by pranav283
I have never done it..but it depends on whether you want to store all this new data into a new table.
If yes, then I guess this is what you will have to do..
1. Select the rows from original table into the host variables of the cursor. I suppose you will have to select these rows into a 2d array (one to hold the new column and one to hold the new rows for that column)
2. Then, if you have to store these into a new table keeping in mind the original storing sequence, then start reading that COBOL table one by one for each state (also holding their values) and INSERT them into the new table.
But again, this may not be sole solution. Since, the requirement is a little bit complex, you may have to redesign your pseudo code repeatedly and gradually depending on whether or not it is giving the required solution.
Re: Recursive SQL in DB2
Posted:
Sun Nov 20, 2016 12:00 pm
by subratarec
@pawarsandip21 - I also have same kind of query. So just wanted to know if you got the solution for the same? If you got could you please share the same here?
Thanks in advance.
Re: Recursive SQL in DB2
Posted:
Sun Nov 20, 2016 5:20 pm
by BillyBoyo
Some trivial search-engining reveals examples using CTE. It shouldn't be difficult to work from those. To note, it seems that OLAP and XML processing can also achieve the same thing in DB/2 for z/OS. Is it a competition? Are you limited to using CTE?
Re: Recursive SQL in DB2
Posted:
Sun Nov 20, 2016 6:30 pm
by subratarec
Hi Billy,
Yes in my case I am limited to CTE. This is kind of new to me (using CTE write recursive query)
Thanks
Re: Recursive SQL in DB2
Posted:
Sun Nov 20, 2016 7:18 pm
by NicC
I have seen this kind of transfomation somewhere - probably in the SQLite mailing list. The archive for this is a held in a Nabble forum.
I'd suggest getting a copy of SQLite downloaded (free) onto your home PC and use it for learning. Join the mailing list or use the archive. When searching the archive for CTE related queries search for posts after 2015 as CTEs were introduced some time in 2014.
Re: Recursive SQL in DB2
Posted:
Tue Nov 22, 2016 6:01 pm
by GuyC