Re: Where Clause on Nested Selects

"Jamie Townsend" <jamie_townsend_at_hotmail.com> 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'
|
| )

for version: 'select * from v$version'

for your sql problem, if your subquery returns a null, the condition 'and not ma_id in (...)' will never be true ... it will never be false, either

simple example:

SQL> select ename
  2 from emp
  3 where not job in (
  4 select null
  5 from dual
  6 union
  7 select 'CLERK'
  8 from dual
  9 )
 10 /

no rows selected

this is because NULL is technically an unknown value, so we don't really know if a particular job is equal to, or not equal to, an unknown value

simply filter nulls from you subquery, and life will be good

;-{ mcs Received on Mon Mar 01 2004 - 17:11:14 CET