We help IT Professionals succeed at work.

Check out our new AWS podcast with Certified Expert, Phil Phillips! Listen to "How to Execute a Seamless AWS Migration" on EE or on your favorite podcast platform. Listen Now

x

boolean NOT does not work in where clause

noreenburke
noreenburke asked
on
Medium Priority
510 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?
Comment
Watch Question

Database Developer & Administrator
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2012
Commented:
Unlock this solution with a free trial preview.
(No credit card required)
Get Preview

Commented:
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

Sean StuberDatabase Developer & Administrator
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2012

Commented:
if that works, then it's definitely a NULL problem
and more easily resolved with

 and LNNVL(column_a = x and column_b = y);
HainKurtSr. System Analyst
CERTIFIED EXPERT

Commented:
try this:

and NOT (column_a = x and column_b = y);
-->
and (column_a != x or column_b !=y)
Sean StuberDatabase Developer & Administrator
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2012

Commented:
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);
HainKurtSr. System Analyst
CERTIFIED EXPERT

Commented:
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

Sean StuberDatabase Developer & Administrator
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2012

Commented:
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
CERTIFIED EXPERT
Top Expert 2008

Commented:
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.
Sean StuberDatabase Developer & Administrator
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2012

Commented:
yes, exactly my point!

what does "does not work" mean to the asker

we can only guess because the syntax itself is fine.
CERTIFIED EXPERT
Top Expert 2008

Commented:
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.
CERTIFIED EXPERT
Top Expert 2008

Commented:
Mr. Stuber, sorry!
Sean StuberDatabase Developer & Administrator
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2012

Commented:
no problem, I was simply agreeing that we were saying the same thing

Author

Commented:
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.



Guy Hengel [angelIII / a3]Billing Engineer
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2009

Commented:
so, you want:

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

Open in new window


Sean StuberDatabase Developer & Administrator
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2012

Commented:
that's a repeat of http:#35442770

Author

Commented:
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

Sean StuberDatabase Developer & Administrator
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2012

Commented:
I have a guess , but please provide sample data and expected output
Guy Hengel [angelIII / a3]Billing Engineer
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2009

Commented:
indeed, sorry for "repeating", I had not seen it.

Author

Commented:
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


Author

Commented:
IT was the closest
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a free trial preview!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.