Where Clause on Nested Selects

[Jamie Townsend]

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'

)

[Mark C. Stock]

"Jamie Townsend" <ja************@hotmail.com> wrote in message
news:3e*************************@posting.google.co m...
| 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

[Fay]

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
ja************@hotmail.com (Jamie Townsend) wrote in message news:<3e*************************@posting.google.c om>...

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'

)