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

Can the this be done using single DB2 query?


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

New User


Joined: 14 Sep 2005
Posts: 74
Location: Atlanta, (USA)

PostPosted: Wed Jan 27, 2010 7:23 pm
Reply with quote

Hi,

I have below 2 tables

Code:

Table A

DATE1      DATE2      DATE3
2009-11    2009-12    2010-01


and

Code:

Table B

DATEA      COUNT1      COUNT2
2009-11     003           002
2010-01     004           005


I require the below output

Code:

DATEA      COUNT1        COUNT2
2009-11     003            002
2009-12     000            000
2010-01     004            005


Can we do the above using DB2 query?
Back to top
View user's profile Send private message
rockish

Active User


Joined: 05 Jun 2009
Posts: 185
Location: Planet Earth

PostPosted: Thu Jan 28, 2010 2:46 am
Reply with quote

Do you mean to say Date1, Date2 and Date3 are three column names in Table A ?

Also pls confirm if the Table A can contain only row or will it have more than one rows ?
Back to top
View user's profile Send private message
Escapa

Senior Member


Joined: 16 Feb 2007
Posts: 1399
Location: IL, USA

PostPosted: Thu Jan 28, 2010 3:08 am
Reply with quote

Can you tell the business requirement of doing so?

For me it looks very bad design to have one column in a table for one date and new column for new date and so on....
icon_rolleyes.gif
Back to top
View user's profile Send private message
sushanth bobby

Senior Member


Joined: 29 Jul 2008
Posts: 1020
Location: India

PostPosted: Thu Jan 28, 2010 2:48 pm
Reply with quote

Muffi,

Non-Tested Code
Code:
select a.date1,coalesce(b.count1,0),coalese(b.count2,0)
from tablea a, tableb b
where a.date1=b.datea
union
select a.date2,coalesce(b.count1,0),coalesce(b.count2,0)
from tablea a, tableb b
where a.date2=b.datea
union
select a.date3,coalesce(b.count1,0),coalesce(b.count2,0)
from tablea a, tableb b
where a.date3=b.datea


Sushanth
Back to top
View user's profile Send private message
bhairon singh rathore

New User


Joined: 19 Jun 2008
Posts: 91
Location: banglore

PostPosted: Thu Jan 28, 2010 3:04 pm
Reply with quote

Muffi
Please find alternate way to implement
Code:
SELECT DATEA,MAX(COUNT1),MAX(COUNT2) from (
SELECT DATEA,COUNT1,COUNT2 FROM SESSION.TABLEB
unION
SELECT DATE1 AS DATEA,'000' AS COUNT1,'000' AS COUNT2
FROM SESSION.TABLEA
union
seLECT DATE2 AS DATEA,'000' AS COUNT1,'000' AS COUNT2
FROM SESSION.TABLEA
unION
seLECT DATE3 AS DATEA,'000' AS COUNT1,'000' AS COUNT2
FROM SESSION.TABLEA
) TABLED
GROUP BY DATEA;


Please note count1 and count2 is taken as CHAR
Change as per your requirement.
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 RC query -Time column CA Products 3
No new posts Dynamically pass table name to a sele... DB2 2
No new posts How to append a PS file into multiple... JCL & VSAM 3
No new posts Query on edit primary command CLIST & REXX 5
No new posts Query on edit primary command CLIST & REXX 1
Search our Forums:

Back to Top