68

The following works - returns Y when chargeback equal to 1 else it defaults to N

IF(fd.charge_back = 1, 'Y', 'N') AS charge_back

however I cannot seem to get this one working? Is the syntax valid

IF(compliment = ('set' OR 'Y' OR 1), 'Y', 'N') AS customer_compliment
Robbo_UK
  • 11,351
  • 25
  • 81
  • 117
  • 8
    What if you change your condition to `compliment IN ('set','Y','1')` – Lamak Feb 02 '12 at 12:10
  • 1
    You might consider just not doing this in your query. This is formatting, not querying anymore. You get a clear true/false 1/0 in return which should do the trick. For example you could do: if(compliment) etc which cannot be done with a string like Y/N. Also Y/N is language agnostic while the boolean is not. So when translating to dutch for example you would have to convert Y/N again to J/N. In short: Maybe just don't do this unless you have a very good reason to do so or just have to take a cut-off solution. – Luc Franken Feb 02 '12 at 12:21
  • Thanks for your feedback i agree boolean would be a better value to use. However im working with existing data and not able to change all the values without further knock on effect on other parts. – Robbo_UK Feb 02 '12 at 12:52

2 Answers2

118

Presumably this would work:

IF(compliment = 'set' OR compliment = 'Y' OR compliment = 1, 'Y', 'N') AS customer_compliment
Digbyswift
  • 10,310
  • 4
  • 38
  • 66
  • 4
    Maybe a dumb question, but does an `IN` clause work here? E.g. `IF(compliment IN('set','Y',1), 'Y', 'N') AS customer_compliment`? – Buttle Butkus May 22 '16 at 00:26
17
IF(compliment IN('set','Y',1), 'Y', 'N') AS customer_compliment

Will do the job as Buttle Butkus suggested.

Community
  • 1
  • 1
AleXqwq
  • 251
  • 3
  • 6
  • This is a good shout for `VARCHAR` and `INT` values, but does not work with `null`. [Cross-reference here](https://stackoverflow.com/a/6362216/6340496). – S3DEV Apr 09 '21 at 12:03