0

I've tried to follow this answer (to a different problem that what I have). However, I'm getting the error telling me that the syntax is incorrect near IF.

select if(1 < 2, 3, 4) as Reply 
from Unit

The above is the exact syntax I'm using (the table Unit exists but has nothing to do with the values, of course. Sometimes I just wish to be able to manipulate some columns for better visibility when I play in SQL Studio.

Please note that I'm not asking about how to coalesce nor how to handle null values. If it appears like if I am, then either you've misunderstood the question or I haven't been clear with my explanation. Sorry about that in advance.

Community
  • 1
  • 1
Konrad Viltersten
  • 36,151
  • 76
  • 250
  • 438
  • 1
    More recent versions of SQL Server support `iif()` (for, what is in my opinion, a misguided effort to be compatible with Access). You should use the ANSI standard `CASE` for this logic. – Gordon Linoff Sep 26 '14 at 10:58
  • @GordonLinoff Did you really mean *if* with two *i*'s in it? Cute syntax, in such case. – Konrad Viltersten Sep 26 '14 at 10:59
  • 1
    @Konrad Worth noting the answer you link to explicitly states that it doesn't work with MSSQL. That's why, as zaratustra states in their answer, you should use CASE. – Andy Nichols Sep 26 '14 at 10:59
  • @AndyNichols Well, it stated that it **could** when I read it because my eyes missed the negation, haha. Of course, now that you point that out, I see my obvious mistake. Stupid eyes... :) – Konrad Viltersten Sep 26 '14 at 11:01
  • 1
    @KonradViltersten . . . Yes, I really do mean `iif()` with two "i"s. Learn to use `case`. You might imagine that I think there are way more important things that SQL Server developers could do than implement Access functions. (Aggregate string concatenation anyone?) – Gordon Linoff Sep 26 '14 at 11:09

2 Answers2

2

You are looking for the case clause:

select 
  case when 1 < 2 
    then 3 
    else 4 end as Reply 
from Unit

Or if you only wish to see the result once (as opposed to for each row in the table):

select
  case when 1 < 2 then 3 else 4 end as Reply
Konrad Viltersten
  • 36,151
  • 76
  • 250
  • 438
neshkeev
  • 6,280
  • 3
  • 26
  • 47
1

I believe this is the syntax you are looking for, it works from sqlserver 2012:

SELECT IIF( 1 < 2, 3, 4)

The syntax is:

IIF ( boolean_expression, true_value, false_value )

t-clausen.dk
  • 43,517
  • 12
  • 59
  • 92
  • Unfortunately, one of my environments is SQL Server Tyrannosaurus Rex so I'll have to *CASE* my way through. Nevertheless, I'll swiiiitch to *IIIIF* statement (typo intended) as soon as we get on the right side of the millennium for **all** our servers. – Konrad Viltersten Sep 26 '14 at 11:20