Re: Where Clause on Nested Selects

Jamie,
Look at the 'where' clause between the two, the problem is here: first sql: "...and rep_fs_funds_of_sac_inc_exc.end_dt is null..." second sql: "...and rep_fs_funds_of_sac_inc_exc.end_dt =rep_fs_funds_of_ac_inc.end_dt..."

You can only use "is null"/"is not null" to compare null value, "=" sign doesn't work for null value (like in your second sql), the concept is that there's no value to be equal(=) to.

Another thing looks interesting in your sql is "...not ma_id in...", we usually use "...ma_id not in...".

Hope this helps.
Fay

jamie_townsend_at_hotmail.com (Jamie Townsend) wrote in message news:<3e4da5e.0403010758.20dd8f59_at_posting.google.com>...
> Hi. I'm having trouble filtering some nested selects. Can anyone
> tell me why these two statements (below) don't return the same
> results? The second example works as expected, but the first example
> doesn't remove the records it should find in the sub-select.
>
> Is this perhaps an oracle bug?
>
> I'm not the Oracle DBA, but we're using something like 8.1.
>
> Any tips greatly appreciated.
>
>
> Thanks,
> Jamie
>
>
> --------
>
> select *
> from rep_fs_funds_of_ac_inc
> where AGREEMENT_NAME = 'Jamie'
> AND END_DT is null
> AND ENTITY_CLASS_ADVANCED_START_DT = '01.01.00'
> and not ma_id in
> (
> select ma_id
> from rep_fs_funds_of_sac_inc_exc
> where comp_basis_relation_typ_id = 1
> and rep_fs_funds_of_sac_inc_exc.end_dt =
> rep_fs_funds_of_ac_inc.end_dt
> and rep_fs_funds_of_sac_inc_exc.agreement_name =
> rep_fs_funds_of_ac_inc.agreement_name
> AND rep_fs_funds_of_sac_inc_exc.ENTITY_CLASS_ADVANCED_START_DT =
> rep_fs_funds_of_ac_inc.ENTITY_CLASS_ADVANCED_START_DT
>
> )
>
>
> --------
>
> select *
> from rep_fs_funds_of_ac_inc
> where AGREEMENT_NAME = 'Jamie'
> AND END_DT is null
> AND ENTITY_CLASS_ADVANCED_START_DT = '01.01.00'
> and not ma_id in
> (
> select ma_id
> from rep_fs_funds_of_sac_inc_exc
> where comp_basis_relation_typ_id = 1
> and rep_fs_funds_of_sac_inc_exc.end_dt is null
> and rep_fs_funds_of_sac_inc_exc.agreement_name = 'Jamie'
> AND rep_fs_funds_of_sac_inc_exc.ENTITY_CLASS_ADVANCED_START_DT =
> '01.01.00'
>
> )
Received on Mon Mar 01 2004 - 23:32:25 CET