Re: converting Informix outer to Postgres
От | George Pavlov |
---|---|
Тема | Re: converting Informix outer to Postgres |
Дата | |
Msg-id | 8C5B026B51B6854CBE88121DBF097A86592AB1@ehost010-33.exch010.intermedia.net обсуждение исходный текст |
Ответ на | converting Informix outer to Postgres (gurkan@resolution.com) |
Список | pgsql-sql |
Why don't you start by presenting the query in a more readable form (hint use SQL-standard JOIN syntax for all of your joins) and maybe narrowing just to a couple of tables to isolate the problem. As it is, it is hard to understand. In the process of rewriting you will be forced to think about each join and maybe clarify the query for yourself. So, for example, maybe this is the query you want (just an attempt to quickly rewrite the Informix using standard JOIN syntax--I have a hunch that you want only one outer join): select count(u.id) from user u inner join invention i on u.id = i.user_id inner join inv_contracts ic on ic.inv_id = i.id inner join milestonedefmdef1 on ic.contract_id = mdef1.contract_id inner join milestonedef mdef2 on ic.contract_id = mdef2.contract_idinner join inv_milestones im1 on im1.inv_id = i.id and mdef1.id = im1.milestone_id left join inv_milestonesim2 on im2.inv_id = i.id and mdef2.id = im2.milestone_id where im1.datesent BETWEEN '2005-05-05' AND '2005-05-10' and im1.datereceived IS NULL ; > -----Original Message----- > From: pgsql-sql-owner@postgresql.org > [mailto:pgsql-sql-owner@postgresql.org] On Behalf Of > gurkan@resolution.com > Sent: Monday, November 06, 2006 4:11 PM > To: pgsql-sql@postgresql.org > Subject: [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 >
В списке pgsql-sql по дате отправления: