From: | gurkan(at)resolution(dot)com |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | converting Informix outer to Postgres |
Date: | 2006-11-07 23:35:05 |
Message-ID: | 1162942505.45511829704ef@www.resolution.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
From | Date | Subject | |
---|---|---|---|
Next Message | redhog | 2006-11-08 00:14:18 | Stable sort? |
Previous Message | Jeff Davis | 2006-11-07 22:37:49 | Re: WAL ends before end time of backup dump |