Complex Informix OUTER

From: gurkan(at)resolution(dot)com
To: pgsql-sql(at)postgresql(dot)org
Subject: Complex Informix OUTER
Date: 2006-04-18 20:39:35
Message-ID: 1145392775.44454e87a8762@www.resolution.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-sql

Hi all.
I have a fairly complicated Informix OUTER needs to be converted
to PostgreSQL and I cannot get around it. For me it is fairly complicated
Problem is inv_contracts OUTERS from three different tables
and two out of these three (invention, 'dbuser as con') needs to
SELECT for data (invention.name as inv_name, con.lastname as con_lastname,
con.firstname as con_firstname)

Thanks for help.

First thought would be below but not allowed, inv_contracts needs to be renamed
and that wont be the correct conversion.

,invention LEFT OUTER JOIN inv_contracts ON inv_contracts.inv_id = invid
,dbuser as con LEFT OUTER JOIN inv_contracts ON inv_contracts.con_id = con.id
,contractDef LEFT OUTER JOIN inv_contracts ON inv_contracts.contract_id IN
(select id FROM contractDef WHERE phase_id = 2);

Possible sub-queries may not be the solution because I need to be able to some
selects from
'dbuser as con' and invention.

Thanks. Any help appriciated.

--ORIGINAL INFORMIX OUTER QUERY
SELECT user.username, user.firstname, user.lastname, add1, add2, city,
stateDef.abbreviation as state, zip, invention.inv_number as invnum,
invention.name as inv_name, con.lastname as con_lastname, con.firstname as
con_firstname
FROM user, ascpDef AS stateDef, address, invention, user as con
, OUTER inv_contracts
WHERE
address.type = 'User' AND
address_id = 1 AND
user.id = address.type_id AND
state_id = stateDef.id AND
invention.user_id = user.id AND
invention.inv_number = '1994376-A' AND
inv_contracts.inv_id = invention.id AND
inv_contracts.con_id = con.id AND
inv_contracts.contract_id IN (select id FROM contractDef WHERE phase_id = 2);

-------------------------------------------------
This mail sent through IMP: www.resolution.com

Browse pgsql-sql by date

  From Date Subject
Next Message Wiebe Cazemier 2006-04-19 12:01:03 Primary key reference count
Previous Message Tom Lane 2006-04-18 19:53:09 Re: create temp table in rule