Re: converting Informix outer to Postgres

I am not familiar with Informix but:

- is OUTER() a LEFT or FULL outer join?
- it is important where you put your join condition in Postgres wrt NULL
insertions of OUTER joins
E.g. Tables A(k,a) with (k1,a1), (k2, a2) records and table B(k,b) with
(k1, b1) will result in:

A LEFT OUTER JOIN B ON a.k = b.k
AxB
k1,a1,k1,b1
k2,a2,NULL,NULL

and

A LEFT OUTER JOIN B ON a.k = b.k WHERE a.k = b.k
AxB
k1,a1,k1,b1

and

A LEFT OUTER JOIN B WHERE a.k = b.k
AxB
k1,a1,k1,b1

Since you moved your join condition from the WHERE to the ON part of the
query, you might run into this subtle difference in joining (been there,
done that ;-)).

Regards,

Harco

gurkan(at)resolution(dot)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
>
> -------------------------------------------------
> This mail sent through IMP: www.resolution.com
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo(at)postgresql(dot)org so that your
> message can get through to the mailing list cleanly
>
>