IBM Mainframe Forum Index
 
Log In
 
IBM Mainframe Forum Index Mainframe: Search IBM Mainframe Forum: FAQ Register
 

DB2 SQL help/idea/suggestion


IBM Mainframe Forums -> DB2
Post new topic   Reply to topic
View previous topic :: View next topic  
Author Message
bshkris

New User


Joined: 21 Mar 2005
Posts: 41
Location: pune

PostPosted: Thu Oct 06, 2016 3:04 pm
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: 446
Location: USA

PostPosted: Thu Oct 06, 2016 7:56 pm
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

Global Moderator


Joined: 21 Sep 2010
Posts: 2727
Location: NYC,USA

PostPosted: Thu Oct 06, 2016 8:57 pm
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

Global Moderator


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

PostPosted: Fri Oct 07, 2016 2:14 pm
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: 1281
Location: Belgium

PostPosted: Fri Oct 07, 2016 7:45 pm
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 View Bookmarks
All times are GMT + 6 Hours
Forum Index -> DB2

 


Similar Topics
Topic Forum Replies
No new posts Any idea about how to solve 3022 aben... IMS DB/DC 3
No new posts Need suggestion on CICS READ UPDATE CICS 8
No new posts Suggestion on KSDS VSAM READ in CICS CICS 5
No new posts Need suggestion on how to force refer... DB2 7
No new posts Need urgent suggestion on DFSORT DFSORT/ICETOOL 6
Search our Forums:

Back to Top