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'
)