Re: Subquery not returning results
From: Michael Austin <maustin_at_firstdbasource.com>
Date: Wed, 08 Apr 2009 13:15:52 -0500
Message-ID: <zR5Dl.25695$yr3.17294_at_nlpi068.nbdc.sbc.com>
Sashi wrote:
> Hi All,
>
> I have a simple subquery:
>
> select ckt_id, charges
> from table A
> where ckt_id not in (select distinct ckt_id from B) and charges > 0
>
> I know of at least one record in A whose ckt_id is not present in
> table B and charges > 0 which is not being returned.
>
> Table A has about 15000 records and B has about 50000 records.
>
> What am I missing here?
>
> Thanks in advance,
> ZSashi
Date: Wed, 08 Apr 2009 13:15:52 -0500
Message-ID: <zR5Dl.25695$yr3.17294_at_nlpi068.nbdc.sbc.com>
Sashi wrote:
> Hi All,
>
> I have a simple subquery:
>
> select ckt_id, charges
> from table A
> where ckt_id not in (select distinct ckt_id from B) and charges > 0
>
> I know of at least one record in A whose ckt_id is not present in
> table B and charges > 0 which is not being returned.
>
> Table A has about 15000 records and B has about 50000 records.
>
> What am I missing here?
>
> Thanks in advance,
> ZSashi
While Oracle does attempt to automatically give your column names an alias, you need to get in the habit of using EXPLICIT names like:
select a.ckt_id, a.charges
from tablea A
where a.ckt_id not in (select distinct b.ckt_id from tableb B)
and a.charges > 0
Received on Wed Apr 08 2009 - 13:15:52 CDT