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
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 |