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_AD VANCED_START_DT = '01.01.00'
and not ma_id in
(
select ma_id
from rep_fs_funds_of _sac_inc_exc
where comp_basis_rela tion_typ_id = 1
and rep_fs_funds_of _sac_inc_exc.en d_dt =
rep_fs_funds_of _ac_inc.end_dt
and rep_fs_funds_of _sac_inc_exc.ag reement_name =
rep_fs_funds_of _ac_inc.agreeme nt_name
AND rep_fs_funds_of _sac_inc_exc.EN TITY_CLASS_ADVA NCED_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_AD VANCED_START_DT = '01.01.00'
and not ma_id in
(
select ma_id
from rep_fs_funds_of _sac_inc_exc
where comp_basis_rela tion_typ_id = 1
and rep_fs_funds_of _sac_inc_exc.en d_dt is null
and rep_fs_funds_of _sac_inc_exc.ag reement_name = 'Jamie'
AND rep_fs_funds_of _sac_inc_exc.EN TITY_CLASS_ADVA NCED_START_DT =
'01.01.00'

)

[Mark C. Stock]

Re: Where Clause on Nested Selects


"Jamie Townsend" <jamie_townsend @hotmail.comwro te in message
news:3e4da5e.04 03010758.20dd8f 59@posting.goog le.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_AD VANCED_START_DT = '01.01.00'
| and not ma_id in
| (
| select ma_id
| from rep_fs_funds_of _sac_inc_exc
| where comp_basis_rela tion_typ_id = 1
| and rep_fs_funds_of _sac_inc_exc.en d_dt =
| rep_fs_funds_of _ac_inc.end_dt
| and rep_fs_funds_of _sac_inc_exc.ag reement_name =
| rep_fs_funds_of _ac_inc.agreeme nt_name
| AND rep_fs_funds_of _sac_inc_exc.EN TITY_CLASS_ADVA NCED_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_AD VANCED_START_DT = '01.01.00'
| and not ma_id in
| (
| select ma_id
| from rep_fs_funds_of _sac_inc_exc
| where comp_basis_rela tion_typ_id = 1
| and rep_fs_funds_of _sac_inc_exc.en d_dt is null
| and rep_fs_funds_of _sac_inc_exc.ag reement_name = 'Jamie'
| AND rep_fs_funds_of _sac_inc_exc.EN TITY_CLASS_ADVA NCED_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:

SQLselect 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]

Re: Where Clause on Nested Selects

Jamie,
Look at the 'where' clause between the two, the problem is here:
first sql: "...and rep_fs_funds_of _sac_inc_exc.en d_dt is null..."
second sql: "...and rep_fs_funds_of _sac_inc_exc.en d_dt
=rep_fs_funds_o f_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


jamie_townsend@ hotmail.com (Jamie Townsend) wrote in message news:<3e4da5e.0 403010758.20dd8 f59@posting.goo gle.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_AD VANCED_START_DT = '01.01.00'
and not ma_id in
(
select ma_id
from rep_fs_funds_of _sac_inc_exc
where comp_basis_rela tion_typ_id = 1
and rep_fs_funds_of _sac_inc_exc.en d_dt =
rep_fs_funds_of _ac_inc.end_dt
and rep_fs_funds_of _sac_inc_exc.ag reement_name =
rep_fs_funds_of _ac_inc.agreeme nt_name
AND rep_fs_funds_of _sac_inc_exc.EN TITY_CLASS_ADVA NCED_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_AD VANCED_START_DT = '01.01.00'
and not ma_id in
(
select ma_id
from rep_fs_funds_of _sac_inc_exc
where comp_basis_rela tion_typ_id = 1
and rep_fs_funds_of _sac_inc_exc.en d_dt is null
and rep_fs_funds_of _sac_inc_exc.ag reement_name = 'Jamie'
AND rep_fs_funds_of _sac_inc_exc.EN TITY_CLASS_ADVA NCED_START_DT =
'01.01.00'
>
)