Portal | Manuals | References | Downloads | Info | Programs | JCLs | Mainframe wiki | Quick Ref
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist 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 query to fetch record which has only ... maxsubrat DB2 12 Mon Dec 11, 2017 5:03 pm
No new posts How to query MAXGENS of an existing P... Chuchulo Hatyzak JCL & VSAM 4 Wed Nov 22, 2017 10:23 pm
No new posts Native SQL Query kishpra DB2 1 Wed Nov 22, 2017 8:38 pm
No new posts Query on IEFBR14 with GDG Ashishpanpaliya JCL & VSAM 4 Tue Nov 07, 2017 8:34 pm
No new posts Formatting multiple records to a sing... Vignesh Sid SYNCSORT 3 Tue Nov 07, 2017 12:22 pm

Facebook
Back to Top
 
Job Vacancies | Forum Rules | Bookmarks | Subscriptions | FAQ | Polls | Contact Us