Received: from chennai.transys (chennai.transys.net [12.33.140.105]) by postgresql.org (8.11.3/8.11.4) with ESMTP id g0HFdwV15898 for ; Thu, 17 Jan 2002 10:39:59 -0500 (EST) (envelope-from unnikrishnan.menon@chennai.transys.net) Received: from UNNIKRISHNAN ([192.168.10.233]) by chennai.transys with SMTP (Microsoft Exchange Internet Mail Service Version 5.5.2650.21) id CGH4ZFWR; Thu, 17 Jan 2002 21:22:39 +0530 Message-ID: <00d101c19f6d$8eba7300$e90aa8c0@UNNIKRISHNAN> From: "Unnikrishnan Menon" To: Subject: Union instead of Outer Join Date: Thu, 17 Jan 2002 21:12:17 +0530 MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_00CE_01C19F9B.A4FF5A30" X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 5.50.4522.1200 X-MimeOLE: Produced By Microsoft MimeOLE V5.50.4522.1200 X-Archive-Number: 200201/200 X-Sequence-Number: 6286 This is a multi-part message in MIME format. ------=_NextPart_000_00CE_01C19F9B.A4FF5A30 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Hi, I have this query in oracle which I'am trying to port to PostgreSQL : Select=20 item_code, nvl(user_id,' ') as user_id=20 from=20 item_list L, User_detail B, User_detail S where=20 L.user_id =3D S.User_id and L.other_userid =3D B.user_id(+) and L.item_code =3D 1234; This query gives me 1 row as result. L.other_userid could be null. I try changing the query thus in postgreSQL : Select=20 item_code, nvl(user_id,' ') as user_id=20 from=20 item_list L, User_detail B, User_detail S where=20 L.user_id =3D S.User_id and L.other_userid =3D B.user_id and L.item_code =3D 1234 Union Select=20 item_code, nvl(user_id,' ') as user_id=20 from=20 item_list L, User_detail B, User_detail S where=20 L.user_id =3D S.User_id and L.item_code =3D 1234 and 0 =3D ( Select=20 count(*)=20 from=20 listed_items L, user_detail B, user_detail S where=20 L.other_userid =3D B.user_id); The above query does not return any row. Where could I be going wrong? Any help would be appreciated. Thanx in advance Unni ------=_NextPart_000_00CE_01C19F9B.A4FF5A30 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable
Hi,
 
I have this query in oracle which I'am try= ing to=20 port to PostgreSQL :
 
Select
    item_code, nvl(user_id,= ' ') as=20 user_id
from
    item_list L, User_detai= l B,=20 User_detail S
where
    L.user_id =3D S.User_id= =20 and
    L.other_userid =3D B.us= er_id(+)=20 and
    L.item_code =3D 1234;
 
This query gives me 1 row as result. L.oth= er_userid=20 could be null.
 
I try changing the query thus in postgreSQ= L=20 :
 
Select
    item_code, nvl(user_id,= ' ') as=20 user_id
from
    item_list L, User_detai= l B,=20 User_detail S
where
    L.user_id =3D S.User_id= =20 and
    L.other_userid =3D B.us= er_id=20 and
    L.item_code =3D 1234
Union
Select
    item_code, nvl(user_id,= ' ') as=20 user_id
from
    item_list L, User_detai= l B,=20 User_detail S
where
    L.user_id =3D S.User_id= =20 and
    L.item_code =3D 1234 and
    0 =3D ( Select
           &nbs= p;    =20 count(*)
            from
           &nbs= p;     listed_items=20 L, user_detail B, user_detail S
            where
              &= nbsp;=20 L.other_userid =3D B.user_id);
 
The above query does not return any row. Where could I be going=20 wrong?
 
Any help would be appreciated.
 
Thanx in advance
 
Unni
------=_NextPart_000_00CE_01C19F9B.A4FF5A30--