From: Unnikrishnan Menon <unnikrishnan.menon@chennai.transys.net>
To: pgsql-sql@postgresql.org
Subject: Union instead of Outer Join
Date: Thu, 17 Jan 2002 21:12:17 +0530
Message-ID: <00d101c19f6d$8eba7300$e90aa8c0@UNNIKRISHNAN> (raw)
Hi,
I have this query in oracle which I'am trying to port to PostgreSQL :
Select
item_code, nvl(user_id,' ') as user_id
from
item_list L, User_detail B, User_detail S
where
L.user_id = S.User_id and
L.other_userid = B.user_id(+) and
L.item_code = 1234;
This query gives me 1 row as result. L.other_userid could be null.
I try changing the query thus in postgreSQL :
Select
item_code, nvl(user_id,' ') as user_id
from
item_list L, User_detail B, User_detail S
where
L.user_id = S.User_id and
L.other_userid = B.user_id and
L.item_code = 1234
Union
Select
item_code, nvl(user_id,' ') as user_id
from
item_list L, User_detail B, User_detail S
where
L.user_id = S.User_id and
L.item_code = 1234 and
0 = ( Select
count(*)
from
listed_items L, user_detail B, user_detail S
where
L.other_userid = 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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: unnikrishnan.menon@chennai.transys.net
Subject: Re: Union instead of Outer Join
In-Reply-To: <00d101c19f6d$8eba7300$e90aa8c0@UNNIKRISHNAN>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox