SitePoint Sponsor

User Tag List

Results 1 to 4 of 4
  1. #1
    SitePoint Member
    Join Date
    Jul 2003
    Location
    Rochester
    Posts
    14
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    WHY this is not working

    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

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    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/

  3. #3
    Just Blow It bronze trophy
    DaveMaxwell's Avatar
    Join Date
    Nov 1999
    Location
    Mechanicsburg, PA
    Posts
    7,294
    Mentioned
    123 Post(s)
    Tagged
    1 Thread(s)
    OK, let's break down the query that didn't work:
    Code:
    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
    Code:
    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
    Code:
    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.
    Dave Maxwell - Manage Your Site Team Leader
    My favorite YouTube Video! | Star Wars, Dr Suess Style
    Learn how to be ready for The Forums' Move to Discourse

  4. #4
    SitePoint Member
    Join Date
    Jul 2003
    Location
    Rochester
    Posts
    14
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks Guys!!! I am not sure why I got so confused..
    Thanks again
    jani


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •