From: gurkan@resolution.com
To: pgsql-sql@postgresql.org
Subject: converting Informix outer to Postgres
Date: Mon, 06 Nov 2006 19:10:31 -0500 (EST)
Message-ID: <1162858231.454fcef749413@www.resolution.com> (raw)
Hi all,
I have been working on this Informix SQL query which has an outer join.
I have attached Informix query and my "supposedly" solution to this query
but I cannot get the same count. I appreciate for any help.
Thanks.
--Informix query
select count(u.id)
from user u, invention i, inv_contracts ic, inv_milestones im1, milestonedef mdef1,
OUTER inv_milestones im2,
milestonedef mdef2
where u.id = i.user_id and
ic.inv_id = i.id and
ic.contract_id = mdef1.contract_id and
im1.inv_id = i.id and
mdef1.id = im1.milestone_id and
im1.datesent BETWEEN '2005-05-05' AND '2005-05-10' and
ic.contract_id = mdef2.contract_id and
im2.inv_id = i.id and
mdef2.id = im2.milestone_id and
im1.datereceived IS NULL
--Postges query
select count(u.id)
from dbuser u, inv_contracts ic, inv_milestones im1, milestonedef mdef1,
--OUTER inv_milestones im2,
milestonedef mdef2 LEFT OUTER JOIN inv_milestones im2 ON mdef2.id = im2.milestone_id
LEFT OUTER JOIN invention i ON im2.inv_id = i.id
where u.id = i.user_id and
ic.inv_id = i.id and
ic.contract_id = mdef1.contract_id and
im1.inv_id = i.id and
mdef1.id = im1.milestone_id and
im1.datesent BETWEEN '2005-05-05' AND '2005-05-10' and
ic.contract_id = mdef2.contract_id and
--im2.inv_id = i.id and
--mdef2.id = im2.milestone_id and
im1.datereceived IS NULL
-------------------------------------------------
This mail sent through IMP: www.resolution.com
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: gurkan@resolution.com
Subject: Re: converting Informix outer to Postgres
In-Reply-To: <1162858231.454fcef749413@www.resolution.com>
* 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