Where Clause on Nested Selects

From: Jamie Townsend <jamie_townsend_at_hotmail.com>
Date: 1 Mar 2004 07:58:09 -0800
Message-ID: <3e4da5e.0403010758.20dd8f59@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 - 09:58:09 CST