Query Conversion: Urgent

Posted in 2000

Sarah Jessica — Thu, 10 Feb 2000 14:39:01 EST — source: Informix-list mailing list archive (1991-1998)

Hi, I have got serious problem when running the query. The below is the Oracle query which I have to run in Informix 8.2 select invoice,code,acct from account where invoice in (select invoice from account minus select cust_invoice from custaccount) So, how can I write this query same as in Informix Thanks ______________________________________________________ Get Your Private, Free Email at http://www.hotmail.com

↪ replying to Sarah Jessica

Doug Agnew — Thu, 10 Feb 2000 15:54:04 -0500 — source: Informix-list mailing list archive (1991-1998)

try: select invoice, code, acct from account where not exists (select * from custaccount where custaccount.cust_invoice equal account.invoice) HTH Doug "Sarah Jessica" <sarah_jessica@hotmail.com> wrote in message news:87v69p$1r3$1@news.xmission.com... > > Hi, > I have got serious problem when running the query. The below is the Oracle > query which I have to run in Informix 8.2 > select invoice,code,acct > from account > where invoice in > (select invoice from account > minus > select cust_invoice from custaccount)> > So, how can I write this query same as in Informix > > Thanks > > ______________________________________________________ > Get Your Private, Free Email at http://www.hotmail.com >

↪ replying to Doug Agnew

Jonathan Leffler — Sat, 12 Feb 2000 06:02:11 GMT — source: Informix-list mailing list archive (1991-1998)

That's a correlated subquery. Ouch! What about: SELECT invoice, code, acct FROM Account WHERE invoice NOT IN (SELECT invoice FROM CustAccount); Doug Agnew wrote: > try: > > select invoice, code, acct from account > where not exists (select * from custaccount where custaccount.cust_invoice > equal account.invoice)> > HTH > Doug > "Sarah Jessica" <sarah_jessica@hotmail.com> wrote in message > news:87v69p$1r3$1@news.xmission.com... > > > > Hi, > > I have got serious problem when running the query. The below is the > Oracle > > query which I have to run in Informix 8.2 > > select invoice,code,acct > > from account > > where invoice in > > (select invoice from account > > minus > > select cust_invoice from custaccount)> > > > So, how can I write this query same as in Informix -- Jonathan Leffler (jleffler@informix.com, jleffler@earthlink.net) Guardian of DBD::Informix v0.95 -- see http://www.perl.com/CPAN #include <disclaimer.h>

↪ replying to Jonathan Leffler

June Tong — Wed, 16 Feb 2000 00:39:02 GMT — source: Informix-list mailing list archive (1991-1998)

Jonathan Leffler wrote: > > That's a correlated subquery. Ouch! > > What about: > > SELECT invoice, code, acct FROM Account > WHERE invoice NOT IN (SELECT invoice FROM CustAccount);> > Doug Agnew wrote: > > > try: > > > > select invoice, code, acct from account > > where not exists (select * from custaccount where custaccount.cust_invoice > > equal account.invoice)> > > > HTH > > Doug > > "Sarah Jessica" <sarah_jessica@hotmail.com> wrote in message > > news:87v69p$1r3$1@news.xmission.com... > > > > > > Hi, > > > I have got serious problem when running the query. The below is the > > Oracle > > > query which I have to run in Informix 8.2 > > > select invoice,code,acct > > > from account > > > where invoice in > > > (select invoice from account > > > minus > > > select cust_invoice from custaccount) No, if I understand what Oracle's MINUS function does, it would be: SELECT invoice, code, acct FROM account WHERE invoice IN ( SELECT invoice FROM account) AND invoice NOT IN ( SELECT cust_invoice FROM custaccount) I believe MINUS removes rows in the second select from the set returned by the first select. June -- june_t@hotmail.com Living on Starbucks Cafe Mocha.