[SQL] SQL help (Informix outer to EnterpriseDB outer)
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 [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] SQL help (Informix outer to EnterpriseDB outer)
On Wednesday 12 April 2006 12:49, [EMAIL PROTECTED] 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 [EMAIL PROTECTED] 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) ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[SQL] trigger to enforce FK with nulls?
[PostgreSQL 8.1.3 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 4.0.1] I'm starting to use lots of foreign key constraints to keep my data clean. In one case, however, I need to allow null values for the key. E.g.: create table opset_steps(opset text, step text, step_num int); create table steps(run text, step text, opset text, user text, step_num int); The constraint on steps should be: steps.(opset,step) must be found in opset_steps.(opset,step) UNLESS steps.opset is null. I started to put dummy rows in opset_steps with opset='NO-OPSET' for each step with no real opset, but this looks really kludgy. Since foreign keys per se can't do this, I presume the way is to use triggers, though I have not used triggers before. Any suggestions, caveats? Opset_steps has only 4400 rows, and inserts to steps are not real frequent, so I don't *think* performace should be a problem. No updates are ever made to steps.opset and steps.step, or to opset_steps.(opset,step) [though updates are often made to *other* fields of steps]. -- George Young -- "Are the gods not just?" "Oh no, child. What would become of us if they were?" (CSL) ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] trigger to enforce FK with nulls?
George Young wrote: > Since foreign keys per se can't do this, I presume the way > is to use triggers, though I have not used triggers before. Says who? Just don't specify NOT NULL on the referencing column. -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] trigger to enforce FK with nulls?
On Wed, 12 Apr 2006, George Young wrote: > [PostgreSQL 8.1.3 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 4.0.1] > > I'm starting to use lots of foreign key constraints to keep my > data clean. In one case, however, I need to allow null values > for the key. E.g.: > > create table opset_steps(opset text, step text, step_num int); > create table steps(run text, step text, opset text, user text, step_num int); > > The constraint on steps should be: >steps.(opset,step) must be found in opset_steps.(opset,step) UNLESS > steps.opset is null. The default foreign key case should not error if either steps.opset or steps.step is null. If you're seeing something else, can you give a complete test case? ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
