WHY this is not working

[Jani]

Hi everyone,

Some query is not working when I use <> operatior.Want to know what I am doing wrong!!!

ORIGINAL ROWS:
----------------------
begin_bal bal final_bal
--------------- --------------- ---------------
-11950336.34 5818859.35 -6131476.99
61649.31 .00 61649.31
209853.33 .00 209853.33
30011365.04 498753.53 30510118.57
9205.00 .00 9205.00
20514769.90 .00 20514769.90
.00 .00 .00
.00 .00 .00
.00 .00 .00
-----------------------------------------
I want to get rid of rows which begin_bal and bal and finalBal =.00
so I gave this criteria which is not working

EX: select begin_bal ,bal, final_bal
from testing
where ((begin_bal <> .00) and
(bal <> .00) and
(final_bal <> .00))
order by fund,acct

This query is working as if I am using as OR operator and returning rows
begin_bal bal final_bal
--------------- --------------- ---------------
-11950336.34 5818859.35 -6131476.99
30011365.04 498753.53 30510118.57

GOT RID OF THE ROWS WHICH begin_bal or bal or final_bal =.00
---------------------------------------

But it worked when I used like this

select * from testing
where not(begin_bal = .00 and
bal = .00 and
final_bal = .00)
order by fund,acct


Anybody has comments on this!!!!


Thanks,
Jani

[r937]

yes, what you got is perfectly normal and correct

NOT(a AND b AND c) is the same as
(NOT a) OR (NOT b) OR (NOT c)

rudy
http://r937.com/

[DaveMaxwell]

OK, let's break down the query that didn't work:

select begin_bal ,bal, final_bal 
  from testing 
where ((begin_bal <> .00) and (bal <> .00) and (final_bal <> .00))
order by fund,acct

This will only return the records that have a balance, a beginning balance and a final balance. If you wanted to find one that had a value in any one of those fields, you would replace the ands with ors

select begin_bal ,bal, final_bal 
  from testing 
where (begin_bal <> .00 or bal <> .00 or final_bal <> .00)
order by fund,acct

or you could re-write it as you had with the NOT operator

select * 
  from testing 
where not(begin_bal = .00 and bal = .00 and final_bal = .00)
 order by fund,acct

The reason your last one worked is like Rudy said. If any one of the conditions inside the not is false, it's going to pass that check.

[Jani]

Thanks Guys!!! I am not sure why I got so confused..
Thanks again
jani