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
[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 --mdef2.id = im2.milestone_id and im1.datereceived IS NULL Is there a reason that these two lines are commented out in the postgresql query? Regards, Richard Broersma Jr. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
