Results 1 to 11 of 11
  1. #1
    Join Date
    Sep 2006
    Posts
    162

    Unanswered: best way to query several fields with certain criteria

    I want to query all records that do not have 0s in a span 20 or so fields.

    Hows the best way to do this?

    For instance, I would want to pull rows 1, 2, 10, and 13



    0 0 0 0 0 0 0 0 0 0 0 0 0 3 0 0 0 0 0 0 0 0 0 0 0 0 0 0
    0 0 0 0 0 0 0 0 0 1 0 0 -1 -2 0 0 0 0 0 0 0 0 0 0 0 0 0 0
    0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
    0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
    0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
    0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
    0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
    0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
    0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
    1 0 0 1 0 0 0 0 0 0 0 0 1 0 0 0 0 0 0 1 0 0 0 0 0 0 0 0
    0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
    0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
    0 0 0 0 0 0 0 0 0 0 -1 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0

  2. #2
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    UGH!!!!!

    ... One very large where clause ...

    This is to cut off the bitch-slapping that's bound to ensue ...

    What is this table? What does each of the columns represent? Can this table be normalized?

    Otherwise ... SELECT * FROM WhoKnowsWhat WHERE ((Col1 <> 0) OR (Col2 <> 0) OR ... OR (ColN <> 0));
    Back to Access ... ADO is not the way to go for speed ...

  3. #3
    Join Date
    Sep 2006
    Posts
    162
    These are Invoice codes. I have two table and a query where I compare the jobs. This way I can see what the difference in what they are paying us and what we think they should be paying us. All the query does is [TblA].[Field1]-[TblB].[Field1

    The negatives is where they payed us more than we anticipated and the positive is where they shorted us.

    Another way may be to add a field in the query that counts how many 0s are in the fields and look for a certain number of 0s? An idea, think that would work better?



    Also sorry for the multi post, system was lagging.

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    i think it would work better if instead of messing around with denormalized 0s and 1s, you ran your audit query against the original tables
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    What about
    Code:
    SELECT * FROM MyTable WHERE
    Sum(abs(field1) + abs(field2) + ... + abs(fieldN)) <> 0
    ?
    George
    Home | Blog

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    without the SUM, george, yes

    and it would only ever be >= 0, never less than, eh
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    *Lol* Obviously.
    I was got confused - I came up with two solutions and I appear to have combined them nicely
    George
    Home | Blog

  8. #8
    Join Date
    Sep 2006
    Posts
    162
    Rgr will try it that way, thanks

  9. #9
    Join Date
    Sep 2006
    Posts
    162
    Was working on trying this after get some lunch and realized that some numbers are neg(-) so it could potentially still add up to 0

  10. #10
    Join Date
    Sep 2006
    Posts
    162
    Seems the really long or statement m owen suggested will be the way.

  11. #11
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    jay, you do realize what the ABS function does, right?

    so adding them up can never be negative
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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