Recursive SQL in DB2



IBM's flagship relational database management system

Recursive SQL in DB2

Postby pawarsandip21 » Mon Nov 14, 2016 9:40 am

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
pawarsandip21
 
Posts: 2
Joined: Sat Nov 12, 2016 4:50 pm
Has thanked: 0 time
Been thanked: 0 time

Re: Recursive SQL in DB2

Postby prino » Mon Nov 14, 2016 1:57 pm

DB2 does not have a recursive query concept! Period. Full stop. End of story.
Robert AH Prins
robert.ah.prins @ the.17+Gb.Google thingy
User avatar
prino
 
Posts: 635
Joined: Wed Mar 11, 2009 12:22 am
Location: Vilnius, Lithuania
Has thanked: 3 times
Been thanked: 28 times

Re: Recursive SQL in DB2

Postby pranav283 » Mon Nov 14, 2016 2:36 pm

@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).
pranav283
 
Posts: 47
Joined: Sat Aug 30, 2014 3:52 pm
Has thanked: 2 times
Been thanked: 0 time

Re: Recursive SQL in DB2

Postby pawarsandip21 » Mon Nov 14, 2016 8:48 pm

@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
pawarsandip21
 
Posts: 2
Joined: Sat Nov 12, 2016 4:50 pm
Has thanked: 0 time
Been thanked: 0 time

Re: Recursive SQL in DB2

Postby pranav283 » Mon Nov 14, 2016 9:01 pm

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.
pranav283
 
Posts: 47
Joined: Sat Aug 30, 2014 3:52 pm
Has thanked: 2 times
Been thanked: 0 time

Re: Recursive SQL in DB2

Postby subratarec » Sun Nov 20, 2016 12:00 pm

@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.
subratarec
 
Posts: 3
Joined: Wed Dec 16, 2009 3:12 pm
Has thanked: 1 time
Been thanked: 0 time

Re: Recursive SQL in DB2

Postby BillyBoyo » Sun Nov 20, 2016 5:20 pm

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?
BillyBoyo
Global moderator
 
Posts: 3804
Joined: Tue Jan 25, 2011 12:02 am
Has thanked: 22 times
Been thanked: 265 times

Re: Recursive SQL in DB2

Postby subratarec » Sun Nov 20, 2016 6:30 pm

Hi Billy,

Yes in my case I am limited to CTE. This is kind of new to me (using CTE write recursive query)

Thanks
subratarec
 
Posts: 3
Joined: Wed Dec 16, 2009 3:12 pm
Has thanked: 1 time
Been thanked: 0 time

Re: Recursive SQL in DB2

Postby NicC » Sun Nov 20, 2016 7:18 pm

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.
The problem I have is that people can explain things quickly but I can only comprehend slowly.
Regards
Nic
NicC
Global moderator
 
Posts: 3025
Joined: Sun Jul 04, 2010 12:13 am
Location: Pushing up the daisies (almost)
Has thanked: 4 times
Been thanked: 136 times

Re: Recursive SQL in DB2

Postby GuyC » Tue Nov 22, 2016 6:01 pm

I can explain it to you, but i can not understand it for you.
GuyC
 
Posts: 315
Joined: Tue Aug 11, 2009 3:23 pm
Has thanked: 1 time
Been thanked: 4 times

Next

Return to DB2