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
---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
