X-Original-To: pgsql-sql@postgresql.org Received: from spampd.localdomain (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id 6F662476304 for ; Thu, 17 Apr 2003 10:28:53 -0400 (EDT) Received: from ls401.hinet.hr (ls401.hinet.hr [195.29.150.2]) by postgresql.org (Postfix) with ESMTP id 022A84762F6 for ; Thu, 17 Apr 2003 10:28:52 -0400 (EDT) Received: from baska (AMDOSOFT.net.hinet.hr [194.152.220.42]) by ls401.hinet.hr (0.0.0/8.12.6) with SMTP id h3HEStnl011285 for ; Thu, 17 Apr 2003 16:28:55 +0200 From: "Marco Roda" To: Subject: OUTER JOIN Date: Thu, 17 Apr 2003 16:30:06 +0200 Message-ID: <000701c304ed$d78ba7c0$0301a8c0@amdosoft.local> MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: 7bit X-Priority: 3 (Normal) X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook 8.5, Build 4.71.2173.0 Importance: Normal X-MimeOLE: Produced By Microsoft MimeOLE V5.50.4133.2400 X-Spam-Status: No, hits=-0.9 required=5.0 tests=BAYES_30,SUBJ_ALL_CAPS version=2.50 X-Spam-Level: X-Spam-Checker-Version: SpamAssassin 2.50 (1.173-2003-02-20-exp) X-Archive-Number: 200304/271 X-Sequence-Number: 12972 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