From: Richard Broersma Jr <rabroersma@yahoo.com>
To: gurkan@resolution.com
To: pgsql-sql@postgresql.org
Subject: Re: converting Informix outer to Postgres
Date: Mon, 6 Nov 2006 16:58:39 -0800 (PST)
Message-ID: <429904.33409.qm@web31812.mail.mud.yahoo.com> (raw)
In-Reply-To: <1162858231.454fcef749413@www.resolution.com>
--- gurkan@resolution.com wrote:
> 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
Is there a reason that these two lines are commented out in the postgresql query?
Regards,
Richard Broersma Jr.
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: rabroersma@yahoo.com, gurkan@resolution.com
Subject: Re: converting Informix outer to Postgres
In-Reply-To: <429904.33409.qm@web31812.mail.mud.yahoo.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