Re: What is wrong with this query?

> joe_at_access1.digex.net (Joe Nardone) writes:
>
> As part of an error trapping routine, I'm having a problem with
> what should be a fairly simple query.
>
> First, the query:
>
> select bill_acct_id from transmission
> where bill_acct_id not in (select acct_id from convert_acct);
>
> Basically, I want a list of all 'bill_acct_id's in the 'transmission'
> table that are not in the conversion table (convert_acct). I know
> for a fact that there are bill_acct_id's not in the conversion
> table, yet the query returns no rows. Why?

Hmmm... It certainly should work, even if it does take forever to return. I can only assume that the subselection actually does return some rows -- I have seen this syntax work, so I cannot say for sure what is happening in your situation. I can't say what the behavior would be if there are NULLs in your CONVERT_ACCT table, though -- this may be the trouble.

However, let me mention that the use of "not in" is not recommended, for performance reasons. A better way to structure the query is as follows:

select bill_acct_id
from transmission, convert_acct
where bill_acct_id = acct_id(+)

    and convert_acct rowid is null;

This behaves similarly, should work perfectly, and perform a little better in any case.

Michael Stowe
Constellation Engineering
http://www.mcs.net/~stowe Received on Mon May 15 1995 - 00:00:00 CEST