SQL help (Informix outer to EnterpriseDB outer) - Mailing list pgsql-sql

Hi all,
I have been working on converting our Informix DB to PostgreSQL. There are some
differences with SQL syntax.

I have done many outer conversion so far, but all has either one outer or simple
one. But this one I do not know how to do it. I have searched but could not find
similar to what I need.

This is the one works on InformixDB (OUTER inv_contracts ) connects to three
different tables (1. inv_contracts.inv_id = invention.id AND
2. inv_contracts.con_id = con.id AND
3. inv_contracts.contract_id IN (select id FROM contractDef WHERE phase_id = 2))

-----informix outer -----------
select count(user.id)
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);
------------------------------

If there were only one table connection (say 1. inv_contracts.inv_id =
invention.id AND )
I would have done it as
(,invention LEFT OUTER JOIN inv_contracts ON inv_contracts.inv_id = invention.id)

but I can do same or similar conversion for 2. and 3.

I have attempted to do as below but not giving correct count.


--------postgres------------------
select count(dbuser.id)
FROM dbuser, ascpDef AS stateDef, address
--, invention
--, dbuser as con
--, OUTER inv_contracts
--1
--,invention LEFT OUTER JOIN inv_contracts ON inv_contracts.inv_id = invention.id
--,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)
--1 gives ERROR:  table name "inv_contracts" specified more than once
--
--2
--,invention LEFT OUTER JOIN inv_contracts ON inv_contracts.inv_id = invention.id
--,dbuser as con LEFT OUTER JOIN inv_contracts as inv2 ON inv2.con_id = con.id
--,contractDef LEFT OUTER JOIN inv_contracts as inv3 ON inv3.contract_id IN
(select id FROM contractDef WHERE phase_id = 2)
--2 takes very long (cancelled/stoped by me) (informix returns 306229 within 10sec)
--Cancel request sent
--ERROR:  canceling statement due to user request
--
--3
--,inv_contracts LEFT OUTER JOIN invention ON inv_contracts.inv_id = invention.id
--,inv_contracts as inv2 LEFT OUTER JOIN dbuser as con ON inv2.con_id = con.id
--,inv_contracts as inv3 LEFT OUTER JOIN contractDef ON inv3.contract_id IN
(select id FROM contractDef WHERE phase_id = 2)
--3 takes very long (cancelled/stoped by me) (informix returns 306229 within 10sec)
--Cancel request sent
--ERROR:  canceling statement due to user request
--
--4
,inv_contracts LEFT OUTER JOIN invention ON inv_contracts.inv_id = invention.id
LEFT OUTER JOIN dbuser as con ON inv_contracts.con_id = con.id
LEFT OUTER JOIN contractDef ON inv_contracts.contract_id IN (select id FROM
contractDef WHERE phase_id = 2)
--4 returns 1 but (informix returns 306229 within 10sec)
WHERE
address.type = 'User' AND
address_id = 1 AND
dbuser.id = address.type_id AND
state_id = stateDef.id AND
invention.user_id = dbuser.id AND
invention.inv_number = '1994376-A';
-------------------------------

Thanks for help.

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