[SQL] regular expression
How do I do regular expression for the problem that I am having I have a string called desc, and say that this string in "TSWUU" -- "" "4 - DSC"-- "4" "6768 - THY" -- "6768" basically string may or may not start with number, I need substring of digits parts "" "4" "6768" - This mail sent through IMP: www.resolution.com ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[SQL] SQL Functions
I have been trying to find a way to return more than one but different types of variables. How do I return more than one but mix types of variables. Any help is appriaciated. Thanks; CREATE FUNCTION allMoney_con(integer,integer,date,date) RETURNS AS ' SELECT DISTINCT(inv_accts.id), inv_accts.description, inv_accts.amtReceived, consultant.lastname, consultant.firstname, consultant.unumber, officeDef.name, companyDef.abbreviation, inv_accts.dateReceived, employee.firstname, employee.lastname, inv_accts.type, contractDef.phase_id FROM dbuser as consultant, employee, inv_accts, officeDef, employee_offices, companyDef, acctDef, inv_contracts, contractDef, invention WHERE consultant.id = employee.user_id AND consultant.id = employee_offices.user_id AND officeDef.id = employee_offices.office_id AND invention.company_id = companyDef.id AND inv_accts.inv_id = inv_contracts.inv_id AND invention.id = inv_contracts.inv_id AND inv_contracts.con_id = consultant.id AND consultant.id = $1 AND invention.id = $2 --AND inv_accts.dateReceived BETWEEN $3 AND $4 AND inv_accts.acct_id = acctDef.id AND acctDef.contract_id = inv_contracts.contract_id AND inv_accts.type NOT LIKE ''DISCOVER%'' AND ( (acctDef.description LIKE ''%PAYMENT%'' --AND acctDef.description NOT LIKE ''3\\%%'' AND strpos(acctDef.description, ''3%'') = 0 AND acctDef.description NOT LIKE ''%DROP SELL%'' ) OR inv_accts.type LIKE ''%BOUNCED CHECK%'' OR inv_accts.description LIKE ''%BC%'' OR inv_accts.type = ''REFUND'' ) AND contractDef.id = inv_contracts.contract_id AND contractDef.id = acctDef.contract_id ORDER BY officeDef.name ASC, consultant.lastname ASC, inv_accts.dateReceived ASC; ' LANGUAGE SQL; - This mail sent through IMP: www.resolution.com ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[SQL] SQL Functions
I have been trying to find a way to return more than one but different types of variables. How do I return more than one but mix types of variables. Any help is appriaciated. Thanks; CREATE FUNCTION allMoney_con(integer,integer,date,date) RETURNS AS ' SELECT DISTINCT(inv_accts.id), inv_accts.description, inv_accts.amtReceived, consultant.lastname, consultant.firstname, consultant.unumber, officeDef.name, companyDef.abbreviation, inv_accts.dateReceived, employee.firstname, employee.lastname, inv_accts.type, contractDef.phase_id FROM dbuser as consultant, employee, inv_accts, officeDef, employee_offices, companyDef, acctDef, inv_contracts, contractDef, invention WHERE consultant.id = employee.user_id AND consultant.id = employee_offices.user_id AND officeDef.id = employee_offices.office_id AND invention.company_id = companyDef.id AND inv_accts.inv_id = inv_contracts.inv_id AND invention.id = inv_contracts.inv_id AND inv_contracts.con_id = consultant.id AND consultant.id = $1 AND invention.id = $2 --AND inv_accts.dateReceived BETWEEN $3 AND $4 AND inv_accts.acct_id = acctDef.id AND acctDef.contract_id = inv_contracts.contract_id AND inv_accts.type NOT LIKE ''DISCOVER%'' AND ( (acctDef.description LIKE ''%PAYMENT%'' --AND acctDef.description NOT LIKE ''3\\%%'' AND strpos(acctDef.description, ''3%'') = 0 AND acctDef.description NOT LIKE ''%DROP SELL%'' ) OR inv_accts.type LIKE ''%BOUNCED CHECK%'' OR inv_accts.description LIKE ''%BC%'' OR inv_accts.type = ''REFUND'' ) AND contractDef.id = inv_contracts.contract_id AND contractDef.id = acctDef.contract_id ORDER BY officeDef.name ASC, consultant.lastname ASC, inv_accts.dateReceived ASC; ' LANGUAGE SQL; - This mail sent through IMP: www.resolution.com ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[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
[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 2: Don't 'kill -9' the postmaster
[SQL] Complex Informix OUTER
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
[SQL] converting Informix outer to Postgres
Hi all, I have been working on this Informix SQL query which has an outer join. I have attached Informix query and my "supposedly" solution to this query but I cannot get the same count. I appreciate for any help. Thanks. --Informix query select count(u.id) from user u, invention i, inv_contracts ic, inv_milestones im1, milestonedef mdef1, OUTER inv_milestones im2, milestonedef mdef2 where u.id = i.user_id and ic.inv_id = i.id and ic.contract_id = mdef1.contract_id and im1.inv_id = i.id and mdef1.id = im1.milestone_id and im1.datesent BETWEEN '2005-05-05' AND '2005-05-10' and ic.contract_id = mdef2.contract_id and im2.inv_id = i.id and mdef2.id = im2.milestone_id and im1.datereceived IS NULL --Postges query select count(u.id) from dbuser u, inv_contracts ic, inv_milestones im1, milestonedef mdef1, --OUTER inv_milestones im2, milestonedef mdef2 LEFT OUTER JOIN inv_milestones im2 ON mdef2.id = im2.milestone_id LEFT OUTER JOIN invention i ON im2.inv_id = i.id where u.id = i.user_id and ic.inv_id = i.id and ic.contract_id = mdef1.contract_id and im1.inv_id = i.id and mdef1.id = im1.milestone_id and im1.datesent BETWEEN '2005-05-05' AND '2005-05-10' and ic.contract_id = mdef2.contract_id and --im2.inv_id = i.id and --mdef2.id = im2.milestone_id and im1.datereceived IS NULL - This mail sent through IMP: www.resolution.com ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] converting Informix outer to Postgres
> --- [EMAIL PROTECTED] wrote: > > > Hi all, > > I have been working on this Informix SQL query which has an outer > join. > > I have attached Informix query and my "supposedly" solution to this > query > > but I cannot get the same count. I appreciate for any help. > > Thanks. > > > > --Informix query > > select count(u.id) > > from user u, invention i, inv_contracts ic, inv_milestones im1, > milestonedef mdef1, > > OUTER inv_milestones im2, > > milestonedef mdef2 > > where u.id = i.user_id and > > ic.inv_id = i.id and > > ic.contract_id = mdef1.contract_id and > > im1.inv_id = i.id and > > mdef1.id = im1.milestone_id and > > im1.datesent BETWEEN '2005-05-05' AND '2005-05-10' and > > ic.contract_id = mdef2.contract_id and > > im2.inv_id = i.id and > > mdef2.id = im2.milestone_id and > > im1.datereceived IS NULL > > > > --Postges query > > select count(u.id) > > from dbuser u, inv_contracts ic, inv_milestones im1, milestonedef > mdef1, > > --OUTER inv_milestones im2, > > milestonedef mdef2 LEFT OUTER JOIN inv_milestones im2 ON mdef2.id = > im2.milestone_id > > LEFT OUTER JOIN invention i ON im2.inv_id = i.id > > where u.id = i.user_id and > > ic.inv_id = i.id and > > ic.contract_id = mdef1.contract_id and > > im1.inv_id = i.id and > > mdef1.id = im1.milestone_id and > > im1.datesent BETWEEN '2005-05-05' AND '2005-05-10' and > > ic.contract_id = mdef2.contract_id and > > --im2.inv_id = i.id and --QUERY1 > > --mdef2.id = im2.milestone_id and --QUERY2 > > im1.datereceived IS NULL > > Is there a reason that these two lines are commented out in the > postgresql query? The reason is that once it is converted to postgres (LEFT OUTER), those two query moved into LEFT OUTER JOIN clause. For simplicity; if the Informix query were as; select count(u.id) from user u, OUTER inv_milestones im2, milestonedef mdef2 where mdef2.id = im2.milestone_id --QUERY2 in Postgres it would have been as; select count(u.id) from dbuser u, --OUTER inv_milestones im2, milestonedef mdef2 LEFT OUTER JOIN inv_milestones im2 ON mdef2.id = im2.milestone_id --where --mdef2.id = im2.milestone_id --QUERY2 In original Informix outer query has one OUTER but has two queries(QUERY1,QUERY2). I can test my conversion at one query a time meaning I can do my conversion if there were only QUERY1 or QUERY2. In my test cases they return the same number on count, but I cannot do it for QUERY1 and QUERY2 at the same time. I have done it before for this kind of query but for this case my solution is not working. thanks for help. - 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
