PostgreSQL SQL - converting Informix outer to Postgres

Author

converting Informix outer to Postgres

gurkan@resolution.com

2006-11-07, 7:18 pm

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

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

George Pavlov

2006-11-07, 7:18 pm

Why don't you start by presenting the query in a more readable form
(hint use SQL-standard JOIN syntax for all of your joins) and maybe
narrowing just to a couple of tables to isolate the problem. As it is,
it is hard to understand. In the process of rewriting you will be forced
to think about each join and maybe clarify the query for yourself. So,
for example, maybe this is the query you want (just an attempt to
quickly rewrite the Informix using standard JOIN syntax--I have a hunch
that you want only one outer join):

select count(u.id)
from user u
inner join invention i
on u.id = i.user_id
inner join inv_contracts ic
on ic.inv_id = i.id
inner join milestonedef mdef1
on ic.contract_id = mdef1.contract_id
inner join milestonedef mdef2
on ic.contract_id = mdef2.contract_id
inner join inv_milestones im1
on im1.inv_id = i.id
and mdef1.id = im1.milestone_id
left join inv_milestones im2
on im2.inv_id = i.id
and mdef2.id = im2.milestone_id
where im1.datesent BETWEEN '2005-05-05' AND '2005-05-10'
and im1.datereceived IS NULL
;

> -----Original Message-----
> From: pgsql-sql-owner@postgresql.org
> [mailto:pgsql-sql-owner@postgresql.org] On Behalf Of
> gurkan@resolution.com
> Sent: Monday, November 06, 2006 4:11 PM
> To: pgsql-sql@postgresql.org
> Subject: [SQL] converting Informix outer to Postgres
>
> 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
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 3: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/docs/faq
>

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

Richard Broersma Jr

2006-11-07, 7:18 pm

--- 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.

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

gurkan@resolution.com

2006-11-07, 7:18 pm

> --- gurkan@resolution.com wrote:
>
> join.
> query
> milestonedef mdef1,
> mdef1,
> im2.milestone_id
>
> 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,QUERY
2). 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

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

Sponsored Links