From: gurkan@resolution.com
To: pgsql-sql@postgresql.org
Subject: Re: converting Informix outer to Postgres
Date: Tue, 07 Nov 2006 18:49:06 -0500 (EST)
Message-ID: <1162943346.45511b720c367@www.resolution.com> (raw)
> --- 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 --QUERY1
> > --mdef2.id = im2.milestone_id and --QUERY2
> > im1.datereceived IS NULL
>
> Is there a reason that these two lines are commented out in the
> postgresql query?
The reason is that once it is converted to postgres (LEFT OUTER), those two query
moved into LEFT OUTER JOIN clause.
For simplicity;
if the Informix query were as;
select count(u.id)
from user u,
OUTER inv_milestones im2,
milestonedef mdef2
where
mdef2.id = im2.milestone_id --QUERY2
in Postgres it would have been as;
select count(u.id)
from dbuser u,
--OUTER inv_milestones im2,
milestonedef mdef2 LEFT OUTER JOIN inv_milestones im2 ON mdef2.id = im2.milestone_id
--where
--mdef2.id = im2.milestone_id --QUERY2
In original Informix outer query has one OUTER but has two
queries(QUERY1,QUERY2). I can test
my conversion at one query a time meaning I can do my conversion if there were
only QUERY1
or QUERY2. In my test cases they return the same number on count, but I cannot do it
for QUERY1 and QUERY2 at the same time. I have done it before for this kind of
query but
for this case my solution is not working.
thanks for help.
-------------------------------------------------
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: <1162943346.45511b720c367@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