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
 

 

Can the this be done using single DB2 query?

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

New User


Joined: 14 Sep 2005
Posts: 74
Location: Kentucky, USA

PostPosted: Wed Jan 27, 2010 7:23 pm    Post subject: Can the this be done using single DB2 query?
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    Post subject:
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    Post subject:
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: 1013
Location: India

PostPosted: Thu Jan 28, 2010 2:48 pm    Post subject:
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    Post subject:
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    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 Join in SQL Query vickey_dw DB2 2 Tue Jan 17, 2017 12:15 am
This topic is locked: you cannot edit posts or make replies. MQ setup query mohitsaini Java & MQSeries 4 Sun Jan 08, 2017 1:53 am
No new posts DB2 query Using Recursion, Converting... smilewithashu2 DB2 1 Tue Jan 03, 2017 12:50 pm
No new posts SQL query to run through list of valu... Ni3-db2 DB2 14 Wed Dec 14, 2016 9:52 am
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


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