Home -> Usenet -> comp.databases.oracle -> Where Clause on Nested Selects

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_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 - 16:58:09 CET












Original text of this message