Re: SQL help (Informix outer to EnterpriseDB outer)

On Wednesday 12 April 2006 12:49, gurkan(at)resolution(dot)com wrote:
> 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
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo(at)postgresql(dot)org so that your
> message can get through to the mailing list cleanly

I could probably work this out for you but I have no time today. However, as a
'plan b' maybe try this...
1- create a temp table based on all tables & conditions in the query except
for the outer table (i.e. user, ascpDef, address, invention, and user)

2- do an outer join on the above temp table and the inv_contracts table
(inv_contracts as the outer)