Page 1 of 2

Recursive SQL in DB2

PostPosted: 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

PostPosted: 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

PostPosted: 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

PostPosted: 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

PostPosted: 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

PostPosted: 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

PostPosted: 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

PostPosted: 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

PostPosted: 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

PostPosted: Tue Nov 22, 2016 6:01 pm
by GuyC