View previous topic :: View next topic
|
Author |
Message |
muffirulz
New User
Joined: 14 Sep 2005 Posts: 74 Location: Atlanta, (USA)
|
|
|
|
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 |
|
|
rockish
Active User
Joined: 05 Jun 2009 Posts: 185 Location: Planet Earth
|
|
|
|
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 |
|
|
Escapa
Senior Member
Joined: 16 Feb 2007 Posts: 1399 Location: IL, USA
|
|
|
|
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....
|
|
Back to top |
|
|
sushanth bobby
Senior Member
Joined: 29 Jul 2008 Posts: 1020 Location: India
|
|
|
|
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 |
|
|
bhairon singh rathore
New User
Joined: 19 Jun 2008 Posts: 91 Location: banglore
|
|
|
|
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 |
|
|
|