A CASE Statement ...with conditions?

• tacy.highland

  Hall of Fame

  Points: 3319

  More actions

  August 10, 2011 at 11:19 am

  #245975

  I have a case statement (in a select statement) I need to run (which doesn't work) that looks something like this:

  CASE WHEN (X = 0

  AND Y = 0

  AND Z = 0)

  THEN Do this

  ELSE ' '

  END

  All 3 conditions need to be satisfied before it applies the "do this".

  Is there any way to actually accomplish this??

  Help?

  Thanks!

• Lowell

  SSC Guru

  Points: 323518

  More actions

  August 10, 2011 at 11:24 am

  #1366765

  SQL is a little different when it comes to CASE statements.a case statement returns a VALUE. it's only used for data elements.

  if you need to do a logical decision, you have to use an IF:

  IF (X = 0 AND Y = 0 AND Z = 0)

  BEGIN

  PRINT 'Do this'

  END

  ELSE

  BEGIN

  PRINT 'Doing something else.'

  END

  for a select statemetn, a classic thing to do is to convert values to be returned:

  SELECT

  OtherColumns,

  CASE

  WHEN someStatus = 1

  THEN 'Open'

  WHEN somestatus = 2 and isShipped = 0

  THEN 'Order Taken'

  WHEN somestatus = 2 and isShipped = 1

  THEN 'Order Shipped'

  ELSE 'Pending'

  END

  FROM SomeTable

  Lowell

  ---

  --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

• tacy.highland

  Hall of Fame

  Points: 3319

  More actions

  August 10, 2011 at 11:52 am

  #1366784

  Thanks so much for your input. Actually, the answer came to me not long after I posted the question. And trust me, it was a "duh" moment...

  I had not accounted for the "ISNULL" I was using to display the results and had forgotten to add it to the case statement. Once I did, it worked great:

  CASE WHEN ISNULL(X,0) = 0

  AND ISNULL(Y,0) = 0

  AND ISNULL(Z,0) = 0

  THEN Do This....

  However, I do like your IF...THEN, so I'll take a look at how that can be applied in the future.

  Thank you so much!

  🙂