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
 

 

DB2 SQL help/idea/suggestion

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

New User


Joined: 21 Mar 2005
Posts: 22
Location: pune

PostPosted: Thu Oct 06, 2016 3:04 pm    Post subject: DB2 SQL help/idea/suggestion
Reply with quote

Hi,
I have table A. the data looks like below

Code:
O_ID          MSR_TYP_CD   MSR_VAL_1_QT   MSR_VAL_2_QT
1          BCCR19                           1   23
2          BCCR19                           1   24
3          BCCR19                           0   23
4          BCCR19                           0   23

Code:
O_ID   MSR_TYP_CD   MSR_VAL_1_QT   MSR_VAL_2_QT
1   BCCR20                          1   1
56   BCCR20                          4   45
33   BCCR20                          0   0
45   BCCR20                          0   0

Code:
O_ID   MSR_TYP_CD   MSR_VAL_1_QT   MSR_VAL_2_QT   
56   BCCR21                           6   0   
67   BCCR21                           1   0   
44   BCCR21                           1   0   
1   BCCR21                           1   1   


the final result should be as below. the val1, val2 columns must have sum of values based upon the O_ID whether it has matching or not.

Code:
O_ID   MSR_TYP_CD   MSR_VAL_1_QT   MSR_VAL_2_QT
1   BCCR59                                3   25
2   BCCR59                                1   0
3   BCCR59                                0   23
4   BCCR59                                0   23
33   BCCR59                                0   0
45   BCCR59                                0   0
56   BCCR59                               10   45
67   BCCR59                                 1   0


BCCR59 is new MSR TYP CD which is combination of BCCR19 + BCCR20 +BCCR21.

Please post your ideas and suggestion how we can do it in DB2 SQL.

Thanks.

CODE' D
Back to top
View user's profile Send private message

RahulG31

Active User


Joined: 20 Dec 2014
Posts: 331
Location: USA

PostPosted: Thu Oct 06, 2016 7:56 pm    Post subject: Reply to: DB2 SQL help/idea/suggestion
Reply with quote

You haven't described it well enough.

In your expected result
    Where has O_ID 44 gone?
    Why O_ID 2 has val1 and val2 as 1 & 0? If I understand it correctly, they should have been 1 & 24.
    Why BCCR(19 + 20 + 21) is Not equal to BCCR(60) ?

Since your question is loosely built, I don't think many people would want to waste their time on this if you don't clarify.

.
Back to top
View user's profile Send private message
Rohit Umarjikar

Senior Member


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

PostPosted: Thu Oct 06, 2016 8:57 pm    Post subject:
Reply with quote

Very poorly described, and you don't need to repeat 3 times the same table data to show us the sample, you could have posted in one shot the combinations.
See, if this simple sql make sense to what you ask.
Code:
select
A.O_ID,
A.MSR1,
sum(A.MSR_VAL_1_QT),
sum(A.MSR_VAL_2_QT)
from
(
select
O_ID,
case when MSR_TYP_CD IN ('BCCR19','BCCR20','BCCR21') then 'BCCR59' else MSR_TYP_CD end as MSR1,
MSR_VAL_1_QT,
MSR_VAL_2_QT
from table) A
group by
A.O_ID,
A.MSR1
Back to top
View user's profile Send private message
Pandora-Box

Moderator


Joined: 07 Sep 2006
Posts: 1529
Location: Andromeda Galaxy

PostPosted: Fri Oct 07, 2016 2:14 pm    Post subject:
Reply with quote

bshkris,

Your query pretty unclear.. Please clarify queries by Rahul.. Your output does not make sense to me atleast..
Back to top
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1278
Location: Belgium

PostPosted: Fri Oct 07, 2016 7:45 pm    Post subject:
Reply with quote

Code:
select O_ID , 'BCCR59',  sum(msr_val_1_qt), sum(msr_val_2_qt)
from mytable
where msr_typ_cd in ('BCCR19','BCCR20','BCCR21')
group by O_ID
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 Suggestion on Search and Split using ... shiitiizz DFSORT/ICETOOL 14 Tue Jun 16, 2015 1:20 pm
No new posts Need Suggestion on -803 error code sambap DB2 8 Fri Apr 04, 2014 5:16 pm
No new posts Need some idea on how get a query rajiv rengasamy DB2 5 Fri Jan 17, 2014 4:01 pm
No new posts Suggestion regarding tool to be used shankarm IBM Tools 9 Thu Jul 25, 2013 11:31 am
No new posts Need suggestion in sort SENTHIL MURUGAAN SYNCSORT 13 Wed Jul 24, 2013 12:00 pm


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