Postgres Pro
Downloads
Home   >   mailing lists

Thread: converting Informix outer to Postgres

converting Informix outer to Postgres

From
gurkan@resolution.com
Date:
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

Re: converting Informix outer to Postgres

From
Martijn van Oosterhout
Date:
On Tue, Nov 07, 2006 at 06:35:05PM -0500, 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.

I don't know what the Informix outer join is, but is it like the SQL
FULL OUTER JOIN? Have you tried using that?

Have a nice day,
--
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> From each according to his ability. To each according to his ability to litigate.
Attachment

Re: converting Informix outer to Postgres

From
"H.J. Sanders"
Date:
Hi.

From some documentation:

In INFORMIX SQL, outer tables are defined in the FROM clause with the OUTER keyword :

    SELECT ... FROM a, OUTER(b)
     WHERE a.key = b.akey

    SELECT ... FROM a, OUTER(b,OUTER(c))
     WHERE a.key = b.akey
       AND b.key1 = c.bkey1
       AND b.key2 = c.bkey2

PostgreSQL 7.1 supports the ANSI outer join syntax :

    SELECT ... FROM cust LEFT OUTER JOIN order
                         ON cust.key = order.custno

    SELECT ...
      FROM cust LEFT OUTER JOIN order
                     LEFT OUTER JOIN item
                     ON order.key = item.ordno
                ON cust.key = order.custno
     WHERE order.cdate > current date







Any help?

Henk


> -----Oorspronkelijk bericht-----
> Van: pgsql-general-owner@postgresql.org
> [mailto:pgsql-general-owner@postgresql.org]Namens Martijn van Oosterhout
> Verzonden: woensdag 8 november 2006 11:42
> Aan: gurkan@resolution.com
> CC: pgsql-general@postgresql.org
> Onderwerp: Re: [GENERAL] converting Informix outer to Postgres
>
>
> On Tue, Nov 07, 2006 at 06:35:05PM -0500, 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.
>
> I don't know what the Informix outer join is, but is it like the SQL
> FULL OUTER JOIN? Have you tried using that?
>
> Have a nice day,
> --
> Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> > From each according to his ability. To each according to his ability to litigate.
>

Re: converting Informix outer to Postgres

From
"Gregory S. Williamson"
Date:
Perhaps a more recent version of postgres (8.1 or mayber even look at 8.2 ...); lots of improvements since 7.1, IIRC in
thearea of joins specifically, but I don't know the answer to your question specifically. 

HTH,

Greg Williamson (a [mostly] former Informix user, but not, alas, with such queries)
DBA
GlobeXplorer LLC


-----Original Message-----
From:    pgsql-general-owner@postgresql.org on behalf of H.J. Sanders
Sent:    Wed 11/8/2006 4:27 AM
To:    Martijn van Oosterhout; pgsql-general@postgresql.org
Cc:
Subject:    Re: [GENERAL] converting Informix outer to Postgres

Hi.

From some documentation:

In INFORMIX SQL, outer tables are defined in the FROM clause with the OUTER keyword :

    SELECT ... FROM a, OUTER(b)
     WHERE a.key = b.akey

    SELECT ... FROM a, OUTER(b,OUTER(c))
     WHERE a.key = b.akey
       AND b.key1 = c.bkey1
       AND b.key2 = c.bkey2

PostgreSQL 7.1 supports the ANSI outer join syntax :

    SELECT ... FROM cust LEFT OUTER JOIN order
                         ON cust.key = order.custno

    SELECT ...
      FROM cust LEFT OUTER JOIN order
                     LEFT OUTER JOIN item
                     ON order.key = item.ordno
                ON cust.key = order.custno
     WHERE order.cdate > current date







Any help?

Henk


> -----Oorspronkelijk bericht-----
> Van: pgsql-general-owner@postgresql.org
> [mailto:pgsql-general-owner@postgresql.org]Namens Martijn van Oosterhout
> Verzonden: woensdag 8 november 2006 11:42
> Aan: gurkan@resolution.com
> CC: pgsql-general@postgresql.org
> Onderwerp: Re: [GENERAL] converting Informix outer to Postgres
>
>
> On Tue, Nov 07, 2006 at 06:35:05PM -0500, 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.
>
> I don't know what the Informix outer join is, but is it like the SQL
> FULL OUTER JOIN? Have you tried using that?
>
> Have a nice day,
> --
> Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> > From each according to his ability. To each according to his ability to litigate.
>

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


-------------------------------------------------------
Click link below if it is SPAM gsw@globexplorer.com

"https://mailscanner.globexplorer.com/dspam/dspam.cgi?signatureID=4551ca60161213366512726&user=gsw@globexplorer.com&retrain=spam&template=history&history_page=1"
!DSPAM:4551ca60161213366512726!
-------------------------------------------------------

Re: converting Informix outer to Postgres

From
Harco de Hilster
Date:
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@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
>
> -------------------------------------------------
> 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@postgresql.org so that your
>        message can get through to the mailing list cleanly
>
>