DB2 LEFT OUTER JOIN 1

[PatelRam]

Help needed in Left Outer Join in Db2
=====================================

Hi,

I am migrating Oracle SQL queries to Db2 queries. How do I write following Left outer join in db2.

1)

Oracle :
========

Select T1.COL1,
T2.COL1,
SUM(T2.COL2)
FROM T1,
( SELECT MAX(DAY) FROM CALENDAR) CALENDAR,
T2
WHERE T1.COL3 = CALENDAR.DAY AND
T1.COL4 = T2.COL4(+)
GROUP BY T1.COL1,T2.COL1


db2 : ???
=====

2)

Oracle :
========


Select A1.COL1,
A1.COL2,
A3.COL1,
A4.COL1
FROM
( Select T1.Col1 COL1,
SUM(T2.Col1) COL2
FROM T1,T2
WHERE T1.COL2 = T2.COL2(+)
GROUP BY T1.COL1 ) A1,
( SELECT MAX(DAY) FROM CALENDAR) CALENDAR,
( SELECT T3.COL1 AS COL1,
T4.COL2 AS COL2,
T3.COL2 AS COL3
FROM T3.COL5 = T4.COL5) A2,
A3
WHERE A1.COL1 = A2.COL1(+)
A2.COL3 = A3.COL3(+)
A2.COL4 = A3.COL4(+)

dB2 : ???
====



Thanks in advanced.

Bhavesh

 

[BrianTyler]

This page will give you everything you need to know, and more, about DB2 SQL

http://ourworld.compuserve.com/homepages/graeme_birchall/HTM_COOK.HTM

 

[PatelRam]

It didn't help me much.

 

[PruSQLer]

That link Brian gave you should have what you need.

Basically, you left outer join in DB2 as follows:

Table1 left outer join Table2
On table1.ID = Table2.ID
and table1.ID2 = Table2.ID2

So, your query should be:


Select T1.COL1,
T2.COL1,
SUM(T2.COL2)
FROM T1 left outer join T2
On t1.col4 = t2.col4 ,
( SELECT MAX(DAY) FROM CALENDAR) CALENDAR,

WHERE T1.COL3 = CALENDAR.DAY

GROUP BY T1.COL1,T2.COL1

 

[PatelRam]

Thank you very much. It worked
I have another similar sql with left outer join and
equi join. Do you know how do I write this:

Oracle :
========
Select T1.COL1,
T2.COL1,
T3.COL1,
SUM(T2.COL2)
FROM T1,
( SELECT MAX(DAY) FROM CALENDAR) CALENDAR,
T2,
T3
WHERE T1.COL3 = CALENDAR.DAY AND
T1.COL4 = T2.COL4(+) AND
T1.COL5 = T3.COL5 AND
GROUP BY T1.COL1,T2.COL1,T3.COL1


Db2 : ????
=====

 

[PruSQLer]

Pretty much the same as the prior one. Keep in mind you'll get nulls on sum(t2.col2) when there is no match.

Select T1.COL1,
T2.COL1,
T3.COL1,
SUM(T2.COL2)
FROM T1 left outer join t2
On T1.COL4 = T2.COL4
,
( SELECT MAX(DAY) FROM CALENDAR) CALENDAR,
T3
WHERE T1.COL3 = CALENDAR.DAY AND
T1.COL5 = T3.COL5 AND
GROUP BY T1.COL1,T2.COL1,T3.COL1