CODEWITHSUNDEEP
sql
RoseChris
RoseChris

Reputation: 51

How to select case when a string is not equal a value then make another field blank?

I have some columns like" OrderfieldNumber, Purchaser, Item. I'd like to create a statement in which: case when the purchaser is not 'ABC', 'DEF', 'GHK' then the OrderFieldNumber is blank. My purpose is just want to show OrderFieldNumber with those buyers only, I want to ignore other buyers without using Filter function. I tried:

case
when purchaser <> 'ABC' then OrderFieldNum = ' '
when purchaser <> 'DEF' then OrderFieldNum = ' '
when purchaser <> 'GHK' then OrderFieldNum = ' '
ELSE purchaser end as FilteredOrderField

But it doesn't seem to work out.

Upvotes: 3

Views: 20205

Answers (2)

juergen d
juergen d

Reputation: 204904

case when purchaser not in ('ABC','DEF','GHK') 
     then ' '
     else OrderFieldNum 
end as FilteredOrderField

Upvotes: 6

Gordon Linoff
Gordon Linoff

Reputation: 1271003

Put it in a single statement, using not in:

(case when purchaser not in ('ABC', 'DEF', 'GHK') then ' '
      else purchaser
 end) as FilteredOrderField

The way you have it written, every non-NULL value will match one of the first two conditions, resulting in a blank.

Also, don't use an = in the then part of the case.

Upvotes: 1

Related Questions