?
Solved

boolean NOT does not work in where clause

Posted on 2011-04-21
20
Medium Priority
?
495 Views
Last Modified: 2012-05-11
Hi Folks:
  I have an sql statement in PL/SQL that is not working as expected.

select columns
  from table
 where key = value
    and column_q = variable_w
    and column_t = variable_p
    and column_a in (a,b,c,d,x)
    and NOT (column_a = x and column_b = y);

The statement compiles but the "NOT (column_a = x and column_b = y)" does not work.

Can you help me with the proper syntax for this condition?
0
Comment
Question by:noreenburke
  • 8
  • 4
  • 3
  • +3
20 Comments
 
LVL 74

Accepted Solution

by:
sdstuber earned 2000 total points
ID: 35442692
"does not work"  - what does this mean?

error at run time? wrong results? no results? keyboard catches fire?


do you have any NULL values?

can you post sample data and expected results for that data?
0
 
LVL 14

Expert Comment

by:ajexpert
ID: 35442698
Can you try this?
select columns
  from table a
 where key = value
    and column_q = variable_w
    and column_t = variable_p
    and column_a in (a,b,c,d,x)
    and NOT EXISTS 
    (SELECT 1 FROM TABLE b
    WHERE 
    A.KEY = B.KEY
    B.column_a = x and B.column_b = y);

Open in new window

0
 
LVL 74

Expert Comment

by:sdstuber
ID: 35442738
if that works, then it's definitely a NULL problem
and more easily resolved with

 and LNNVL(column_a = x and column_b = y);
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 61

Expert Comment

by:HainKurt
ID: 35442770
try this:

and NOT (column_a = x and column_b = y);
-->
and (column_a != x or column_b !=y)
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 35442805
if it's a null issue, that won't resolve it

a=b is FALSE if a or b or both is NULL
a!= b is FALSE if a or b or both is NULL


and, my earlier LNNVL suggestion isn't correct either

lnnvl can only evaluate a single expression
so, it would have to be...

and LNNVL(column_a = x) and LNNVL(column_b = y);
0
 
LVL 61

Expert Comment

by:HainKurt
ID: 35442809
for example this is working fine

with t as (
select 1 id, 'xxx' a, null b from dual
union
select 2, 'hainkurt', 'pwd123' from dual
union
select 3, 'EE', 'pwdee' from dual
) 
--select * from t where not (a='EE' and b='pwd')
select * from t where (a!='EE' or b!='pwd')

ID A B
1 xxx 
2 hainkurt pwd123
3 EE pwdee

Open in new window

0
 
LVL 74

Expert Comment

by:sdstuber
ID: 35442879
HainKurt

oops!, you're right, I was typing faster than I was thinking

since we don't have any sample data or expected results or a clear description of what is going wrong, I was just guessing about nulls and then, lacking a sample didn't test.

so, I agree and retract my "correction" for lnnvl as well
but, that doesn't mean any of the above is actually helpful to the asker.  :)

noreenburke,
as you can see, insufficient detail in the question forces us to guess at what you are looking for
0
 
LVL 48

Expert Comment

by:schwertner
ID: 35442913
The problem is not in NOT operation.
It works without doubt.
the problem is in the expression    (column_a = x and column_b = y)
You have to figure out why you doesn't get the anticipated result.
For instance if you work with dates try to check if the comparisons work.
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 35442934
yes, exactly my point!

what does "does not work" mean to the asker

we can only guess because the syntax itself is fine.
0
 
LVL 48

Expert Comment

by:schwertner
ID: 35456499
So cocentrate on the expression  (column_a = x and column_b = y).

Check the comparisons and check if they are correct, if they work.

We both (with Mr. Sruber) think that there is the problem.
0
 
LVL 48

Expert Comment

by:schwertner
ID: 35456500
Mr. Stuber, sorry!
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 35457268
no problem, I was simply agreeing that we were saying the same thing
0
 

Author Comment

by:noreenburke
ID: 35458960
Hi Folks:
   I apologize, I was in a hurry to get this posted and did not complete my thoughts.

   The statement ---
select columns
  from table
 where key = value
    and column_q = variable_w
    and column_t = variable_p
    and column_a in (a,b,c,d,x)
    and NOT (column_a = x and column_b = y);

does not work in that it removes all occurences of rows that have  "column_a = x" regardless of the contents of "column_b "

When I get my test environment back, I will explore sdstuber's null hypothesis.

Thanks All.



0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 35461367
so, you want:

    and (column_a <> x OR column_b <> y); 

Open in new window


0
 
LVL 74

Expert Comment

by:sdstuber
ID: 35461416
that's a repeat of http:#35442770
0
 

Author Comment

by:noreenburke
ID: 35461759
HI Folks:
   I want to eliminate any rows from the cursor that have an x in column_a AND a y in  collumn_b.

   I have tried "and LNNVL(column_a = x) and LNNVL(column_b = y);" to no avail
   Also did the select where not exists -- to no avail

Thanks

0
 
LVL 74

Expert Comment

by:sdstuber
ID: 35462028
I have a guess , but please provide sample data and expected output
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 35462538
indeed, sorry for "repeating", I had not seen it.
0
 

Author Comment

by:noreenburke
ID: 35465745
Hi Folks:
   I appreciate all your help.
   It turned out to be a problems of NULLS.
   The solution ---  from this "and NOT (column_a = x and column_b = y);"
                              to this     "and NOT (nvl(column_a, q) = x and nvl(column_b, z) = y);"
Thanks all
I guess sdstuber gets the points


0
 

Author Closing Comment

by:noreenburke
ID: 35465750
IT was the closest
0

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Suggested Courses
Course of the Month9 days, 20 hours left to enroll

571 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question