Results 1 to 2 of 2
  1. #1
    Join Date
    Jan 2017
    Posts
    1

    Unanswered: Evaluating the same value in multiple columns in where clause

    I'm relatively new to SQL programming and I am trying to return a set of records, but exclude a set of multiple values that may exist in multiple columns in different tables.
    I'm working with someone else's stored procedure and the piece of logic which is supposed to handle this problem doesn't seem to do that. For example, If any one of the error codes contained in the in operator exist in ad.AccountWarningcode1 or ad.AccountWarningcode2 or ad.AccountWarningcode3 (and so on), then I want those records eliminated from my result set.

    I would greatly appreciate any insight on the most efficient way to approach this, but unless i'm missing something I don't think the Case statement below does the job.

    (Case
    when ad.AccountWarningcode1 IN (98,99,2,29,30,21,10,11,50,53,97) then 1
    when ad.AccountWarningcode2 IN (98,99,2,29,30,21,10,11,50,53,97) then 1
    when ad.AccountWarningcode3 IN (98,99,2,29,30,21,10,11,50,53,97) then 1
    when ad.AccountWarningcode4 IN (98,99,2,29,30,21,10,11,50,53,97) then 1
    when ad.AccountWarningcode5 IN (98,99,2,29,30,21,10,11,50,53,97) then 1
    when ad.AccountWarningcode6 IN (98,99,2,29,30,21,10,11,50,53,97) then 1
    when ad.AccountWarningcode7 IN (98,99,2,29,30,21,10,11,50,53,97) then 1
    when ad.AccountWarningcode8 IN (98,99,2,29,30,21,10,11,50,53,97) then 1
    when ad.AccountWarningcode9 IN (98,99,2,29,30,21,10,11,50,53,97) then 1
    when ad.AccountWarningcode10 IN (98,99,2,29,30,21,10,11,50,53,97) then 1
    when ad.AccountWarningcode11 IN (98,99,2,29,30,21,10,11,50,53,97) then 1
    when ad.AccountWarningcode12 IN (98,99,2,29,30,21,10,11,50,53,97) then 1
    when ad.AccountWarningcode13 IN (98,99,2,29,30,21,10,11,50,53,97) then 1
    when ad.AccountWarningcode14 IN (98,99,2,29,30,21,10,11,50,53,97) then 1
    when ad.AccountWarningcode15 IN (98,99,2,29,30,21,10,11,50,53,97) then 1
    when ad.AccountWarningcode16 IN (98,99,2,29,30,21,10,11,50,53,97) then 1
    when ad.AccountWarningcode17 IN (98,99,2,29,30,21,10,11,50,53,97) then 1
    when ad.AccountWarningcode18 IN (98,99,2,29,30,21,10,11,50,53,97) then 1
    when ad.AccountWarningcode19 IN (98,99,2,29,30,21,10,11,50,53,97) then 1
    when ad.AccountWarningcode20 IN (98,99,2,29,30,21,10,11,50,53,97) then 1
    Else 0 End) = 1

  2. #2
    Join Date
    Oct 2007
    Posts
    151
    Provided Answers: 9
    Maybe, I'm missing your requirement, but wouldn't changing:
    when ad.AccountWarningcode1 IN (98,99,2,29,30,21,10,11,50,53,97) then 1
    when ad.AccountWarningcode2 IN (98,99,2,29,30,21,10,11,50,53,97) then 1

    to:
    when ad.AccountWarningcode1 IN (98,99,2,29,30,21,10,11,50,53,97) then 0
    when ad.AccountWarningcode2 IN (98,99,2,29,30,21,10,11,50,53,97) then 0

    do the trick?

Posting Permissions

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