473,327 Members | 1,967 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,327 software developers and data experts.

Where Clause on Nested Selects

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'

)
Jul 19 '05 #1
2 5232

"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
Jul 19 '05 #2
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'

)

Jul 19 '05 #3

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

47
by: Andrey Tatarinov | last post by:
Hi. It would be great to be able to reverse usage/definition parts in haskell-way with "where" keyword. Since Python 3 would miss lambda, that would be extremly useful for creating readable...
6
by: Christian | last post by:
HI, I have a function that is used to constrain a query: Select COl1, Col2 From MyTable WHERE col1 = ... AND col2 = ... And MyFunction(col1) = ... My problem is that MyFunction is executed...
14
by: joshsackett | last post by:
I have a WHERE clause that could be an "=" or a "LIKE" depending upon if the passed variable is populated or not. I would like to know the best way to write the WHERE clause to make it dynamically...
3
by: Robert Stearns | last post by:
In the query below (in php format, but the intent should be clear), the result set from the sub select is always 1% or less of each of the two tables which are joined in the main query. Is the...
2
by: Jim.Mueksch | last post by:
I am having a problem with using calculated values in a WHERE clause. My query is below. DB2 gives me this error message: Error: SQL0206N "APPRAISAL_LESS_PRICE" is not valid in the context where...
1
by: Jason Hallums | last post by:
New to Mysql; I have created a stored procedure. Using a cursor I traverse the first table. For each row in the cursor I need to determine the correct row in another table to update. Using a...
2
by: Me LK | last post by:
I have a set of drop downs nested in a datagrid. Each drop down is a size or color for an item. Each row of the grid has a button which leads to a cart. When a user selects something from the...
2
by: Jamie Townsend | last post by:
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...
2
by: Cralis | last post by:
Hi guys. I have a database of R/C Helicopters. The user wants to filte down on a specific helicopter configuration. So the GUI has a screen with 5 dropdownlists. ESC (Electric Speed...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.