Query Conversion: Urgent
Sarah Jessica needed an Informix 8.2 equivalent of an Oracle query using MINUS. Doug Agnew proposed a NOT EXISTS subquery; Jonathan Leffler proposed invoice NOT IN (SELECT invoice FROM CustAccount). June Tong said MINUS removes the second set from the first, so the right form is invoice IN (account invoices) AND invoice NOT IN (SELECT cust_invoice FROM custaccount).
Auto-generated by Claude from the posts below — may be imperfect; read the full thread.
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
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
>
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>
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.