DB2 Syntax for Outer join

[Sri]

Hi,

I am new to DB2 and I am using DB2 UDB 8.1 for Windows. I have a query
which works fine in ORACLE and I am trying to find out equivalent
query in DB2.

SELECT
sessi.ses sessions,
sp1.speaker speaker1,
sp2.speaker speaker2,
sp3.speaker speaker3
FROM
(SELECT ses FROM db2admin.sp GROUP BY ses) sessi,
(SELECT ses, speaker FROM sp a WHERE 1 = (SELECT COUNT
(DISTINCT (speaker)) FROM db2admin.sp b WHERE ses = a.ses AND
b.speaker >= a.speaker)) sp1,
(SELECT ses, speaker FROM sp a WHERE 2 = (SELECT COUNT
(DISTINCT (speaker)) FROM db2admin.sp b WHERE ses = a.ses AND
b.speaker >= a.speaker)) sp2,
(SELECT ses, speaker FROM sp a WHERE 3 = (SELECT COUNT
(DISTINCT (speaker)) FROM db2admin.sp b WHERE ses = a.ses AND
b.speaker >= a.speaker)) sp3
WHERE
sessi.ses = sp1.ses(+) AND
sessi.ses = sp2.ses(+) AND
sessi.ses = sp3.ses(+)
ORDER BY
sessi.ses

Here is the output of the above Query
----------------------------------------------
SESSSIONS SPEAKER1 SPEAKER2 SPEAKER3
S1 Surya Srihari Ravi
S2 Savi2 Ravi
S3 Savi3

The actual content of the table is
---------------------------------------------
Sessions Speaker
s1 Surya
s1 Srihari
s1 Ravi
s2 Savi1
s2 Ravi
s3 Savi2

[Serge Rielau]

Mapping from (+) syntax to SQL Standard outer join is described in the
Oracle docs.

Meanwhile try this.. the original SQL hurts my head. :-)
SELECT session,
MAX(case when spno = 1 then speaker else '' end) as speaker1
MAX(case when spno = 2 then speaker else '' end) as speaker2
MAX(case when spno = 3 then speaker else '' end) as speaker3
FROM
(SELECT session,
row_number()
over (partition by session
order by speaker) as spno,
speaker
FROM sp) AS sp
GROUP BY session
ORDER BY session

Cheers
Serge

PS: In case you are porting: The same syntax should work in Oracle as well

[AK]

http://www-106.ibm.com/developerwork...12purcell.html

enjoy!