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
This topic is locked: you cannot edit posts or make replies. SDSF multiple spool datasets extracte... PJAlarcon CLIST & REXX 1 Fri Apr 21, 2017 10:50 pm
No new posts Data replication from multiple Db2 ta... kishpra DB2 9 Mon Mar 27, 2017 9:58 pm
No new posts Splitting of single report to seperat... abdulrafi TSO/ISPF 7 Mon Mar 27, 2017 3:59 pm
No new posts Append data from two files into a sin... Praveen04 DFSORT/ICETOOL 5 Thu Mar 16, 2017 7:29 pm
No new posts JCL to Set Return code based on DB2 S... vinu78 DB2 17 Mon Mar 13, 2017 9:47 pm


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