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/

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

Perhaps a more recent version of postgres (8.1 or mayber even look at 8.2 ...); lots of improvements since 7.1, IIRC in the area 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&amp;user=gsw@globexplorer.com&amp;retrain=spam&amp;template=history&amp;history_page=1&quot;
!DSPAM:4551ca60161213366512726!
-------------------------------------------------------

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: