From: | "Marco Roda" <MarcoRoda(at)amdosoft(dot)com> |
---|---|
To: | <pgsql-sql(at)postgresql(dot)org> |
Subject: | OUTER JOIN |
Date: | 2003-04-17 14:21:36 |
Message-ID: | 000401c304ec$a77547e0$0301a8c0@amdosoft.local |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
Hallo,
I need to port some SQL from Oracle to PostgreSQL v7.2, but I am finding
problems with outer joins.
Here are the tables:
select * from shift_type
id | order_num | from_time | to_time | disabled
----+-----------+-----------+----------+----------
1 | 1 | 06:00:00 | 14:00:00 | f
2 | 2 | 14:00:00 | 22:00:00 | f
3 | 3 | 22:00:00 | 06:00:00 | t
6 | 6 | 00:00:00 | 23:00:00 | f
select * from shift_desc
app_language_id | shift_type_id | description
-----------------+---------------+-------------
1 | 1 | Morning
1 | 2 | Afternoon
1 | 3 | Night
2 | 6 | SHIFT_DE
1 | 6 | SHIFT_EN
and here is the SQL for Oracle:
SELECT ST.id, ST.order_num, ST.from_time, ST.to_time, ST.disabled,
SD.description
FROM shift_type ST, shift_desc SD
WHERE ST.id = SD.shift_type_id(+) AND SD.app_language_id(+) = 2
ORDER BY ST.order_num
The expected result should be:
id | order_num | from_time | to_time | disabled | description
----+-----------+-----------+----------+----------+-------------
1 | 1 | 06:00:00 | 14:00:00 | f |
2 | 2 | 14:00:00 | 22:00:00 | f |
3 | 3 | 22:00:00 | 06:00:00 | t |
6 | 6 | 00:00:00 | 23:00:00 | f | SHIFT_DE
with description populated with NULL, as app_language_id not found.
It seems that Oracle's outer joins work on the basis the single record,
while PostgreSQL don't.
How to do it?
Thank you for your attention,
Marco Roda
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2003-04-17 14:26:43 | Re: planner question.. |
Previous Message | Tom Lane | 2003-04-17 14:17:31 | Re: analyse question.. |