| From: | "Marco Roda" <MarcoRoda(at)amdosoft(dot)com> |
|---|---|
| To: | <pgsql-sql(at)postgresql(dot)org> |
| Subject: | OUTER JOIN |
| Date: | 2003-04-17 14:30:06 |
| Message-ID: | 000701c304ed$d78ba7c0$0301a8c0@amdosoft.local |
| Views: | Whole Thread | Raw Message | 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 | Patrik Kudo | 2003-04-17 14:37:41 | Re: OUTER JOIN |
| Previous Message | Tom Lane | 2003-04-17 14:26:43 | Re: planner question.. |