Re: What is wrong with this query?

On 15 May 1995, Joe Nardone wrote:

> Date: 15 MAY 1995 19:52:04 GMT
> From: Joe Nardone <joe_at_access1.digex.net>
> Newgroups: comp.databases.oracle
> Subject: What is wrong with this 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?
>
> =------------------------------------------------------------------------=
> Joe Nardone | Sundown dazzling day, gold through my eyes, but my eyes
> joe_at_access.digex.net | turned within only see starless and bible black.
>
Joe,

        Is acct_id NULLABLE in the convert_acct table? I've seen this make entire queries return 1403 in PRO*C apps (has to do with the sub-query returning 1405 - a null indicator problem). If so, either make the field non-nullable or add a 'where acct_id not null' -- I gotta assume you index that field too, for performance reasons.

Dave

David B Erickson, Project Leader     * 
McHugh Freeman                       * "YIP YIP YIP YIP YIP YIP YIP YIP YIP"
erickson_at_mfa.com                     *                           -Dino