Re: converting Informix outer to Postgres

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(at)postgresql(dot)org on behalf of H.J. Sanders
Sent: Wed 11/8/2006 4:27 AM
To: Martijn van Oosterhout; pgsql-general(at)postgresql(dot)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(at)postgresql(dot)org
> [mailto:pgsql-general-owner(at)postgresql(dot)org]Namens Martijn van Oosterhout
> Verzonden: woensdag 8 november 2006 11:42
> Aan: gurkan(at)resolution(dot)com
> CC: pgsql-general(at)postgresql(dot)org
> Onderwerp: Re: [GENERAL] converting Informix outer to Postgres
>
>
> On Tue, Nov 07, 2006 at 06:35:05PM -0500, 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.
>
> 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(at)svana(dot)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(at)globexplorer(dot)com
"https://mailscanner.globexplorer.com/dspam/dspam.cgi?signatureID=4551ca60161213366512726&user=gsw(at)globexplorer(dot)com&retrain=spam&template=history&history_page=1"
!DSPAM:4551ca60161213366512726!
-------------------------------------------------------