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
 

 

Recursive query help needed

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

Senior Member


Joined: 21 Sep 2010
Posts: 1609
Location: NY,USA

PostPosted: Mon Feb 27, 2012 7:35 pm    Post subject: Recursive query help needed
Reply with quote

My input table is,

Code:

Acct no     dept         amt
--------    ------   -------
0001        D001       10.00
0002        D001       11.00
0001        D001       08.00
0003        D003       12.12
0003        D004       10.10
0004        D002       12.10


out put should be,

Code:

Acct no        dept1       dept2       dept3       dept4
--------     -------     -------     -------     -------
0001           18.00           0           0           0
0002           11.00           0           0           0
0003               0           0       12.12       10.10
0004               0       12.10           0           0


Could you please suggest the way getting the output done.
I tried with the CASE fuction but somehow couldn't success.
Back to top
View user's profile Send private message

GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1278
Location: Belgium

PostPosted: Mon Feb 27, 2012 8:07 pm    Post subject:
Reply with quote

for a fixed number of departments :
Code:
select acct_no
,sum(case when dept = 'D001' then amt else 0) as dept1
,sum(case when dept = 'D002' then amt else 0) as dept2
,sum(case when dept = 'D003' then amt else 0) as dept3
,sum(case when dept = 'D004' then amt else 0) as dept4
from table1
group by acct_no
Back to top
View user's profile Send private message
Rohit Umarjikar

Senior Member


Joined: 21 Sep 2010
Posts: 1609
Location: NY,USA

PostPosted: Tue Feb 28, 2012 2:35 pm    Post subject:
Reply with quote

Thank you GuyC.
Just a little modification to the output, so i think we need to make a use of recursive query here..Please suggest

Code:

Acct no        dept1       dept2       dept3       dept4
--------     -------     -------     -------     -------
0001           10.00           0           0           0
0001           08.00           0           0           0
0002           11.00           0           0           0
0003               0           0       12.12       10.10
0004               0       12.10           0           0

Back to top
View user's profile Send private message
enrico-sorichetti

Global Moderator


Joined: 14 Mar 2007
Posts: 10201
Location: italy

PostPosted: Tue Feb 28, 2012 2:41 pm    Post subject: Reply to: Recursive query help needed
Reply with quote

Your posts were edited to use the code tags, the make the data more readable by using a fixed font.

I FIXED the poor alignment..

the best way is to edit the text to be posted with a FIXED font editor and then cut and paste.

and ... USE the PREVIEW button to see how the post will look like
Back to top
View user's profile Send private message
Rohit Umarjikar

Senior Member


Joined: 21 Sep 2010
Posts: 1609
Location: NY,USA

PostPosted: Tue Feb 28, 2012 3:08 pm    Post subject:
Reply with quote

@enrico-sorichetti: Sure, I will keep this in my mind.
Back to top
View user's profile Send private message
enrico-sorichetti

Global Moderator


Joined: 14 Mar 2007
Posts: 10201
Location: italy

PostPosted: Tue Feb 28, 2012 3:15 pm    Post subject: Reply to: Recursive query help needed
Reply with quote

the requirement as modified does not really make any sense ( is poorly described / is incomplete)

1) if an account has amounts for just one department ... OK as many lines as the different amounts
2) if an account has ONE amount for each department ... OK consolidate everything on one line
3) BUT if an account has N amounts for M departments what is the logic
Code:

Acct no     dept         amt
--------    ------   -------
0001        D001       10.00
0002        D001       11.00
0001        D001       08.00
0001        D002      110.00
0002        D002      111.00
0001        D002      208.00
0003        D003       12.12
0003        D004       10.10
0004        D002       12.10


how do You want the output ???
Back to top
View user's profile Send private message
Ed Goodman

Active Member


Joined: 08 Jun 2011
Posts: 556
Location: USA

PostPosted: Tue Feb 28, 2012 10:14 pm    Post subject:
Reply with quote

For help with recursive query, see this:
http://ibmmainframes.com/viewtopic.php?t=57640&highlight= icon_biggrin.gif
Back to top
View user's profile Send private message
don.leahy

Active Member


Joined: 06 Jul 2010
Posts: 641
Location: Whitby, ON, Canada

PostPosted: Tue Feb 28, 2012 10:54 pm    Post subject:
Reply with quote

Ed Goodman wrote:
For help with recursive query, see this:
http://ibmmainframes.com/viewtopic.php?t=57640&highlight= icon_biggrin.gif

Good one Ed! You beat me to it! icon_lol.gif icon_lol.gif
Back to top
View user's profile Send private message
Bill Woodger

DFSORT Moderator


Joined: 09 Mar 2011
Posts: 7223

PostPosted: Wed Feb 29, 2012 12:18 am    Post subject: Reply to: Recursive query help needed
Reply with quote

You might find this one useful*.

You won't :-)
Back to top
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1278
Location: Belgium

PostPosted: Wed Feb 29, 2012 3:04 pm    Post subject:
Reply with quote

although it doesn't make sense, if you don't want the sum ; don't use sum() or group by :

Code:
select acct_no
,case when dept = 'D001' then amt else 0 end as dept1
,case when dept = 'D002' then amt else 0 end as dept2
,case when dept = 'D003' then amt else 0 end as dept3
,case when dept = 'D004' then amt else 0 end as dept4
from table1
order by acct_no


ps. I forgot to code the 'end' in my previous post
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
This topic is locked: you cannot edit posts or make replies. Db2 SQL query to convert rows into co... subratarec DB2 5 Thu Nov 17, 2016 4:51 pm
No new posts Two Selects in a single select query Rohit Umarjikar DB2 1 Fri Nov 04, 2016 8:46 pm
No new posts Problem in Running Query via JCL vickey_dw DB2 3 Tue Oct 18, 2016 11:11 pm
No new posts SQL Query optimization. arunsoods DB2 12 Sun Sep 04, 2016 6:27 am
No new posts Query on BLSR Nileshkul JCL & VSAM 2 Sat Aug 13, 2016 5:18 am


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