Q: How can I construct the IIF command in SQL Server?
This is a typical requirement asked by many. This is
for the fact that most of the programmers come from the Access world (where
it is IIF) or the Oracle world (refered as DECODE).
And in this article we address just this unique
requirement of how we can generate an IIF / DECODE construct in SQL Server
2000. Let me illustrate you with a simple example:
Use
Pubs
Go
Select
au_lName, au_FName,
CASE
contract WHEN 1
Then 'Under Contract'
Else 'Not Under Contract'
End
[Contract]
from
authors
Well, What I've done here is that there is a field
in the column as 0 and 1 and for 0 I would like to substitute it with "Not
Under Contract" and for fields value 1 as "Under contract". Just as simple as
that. This is one way to use the CASE expression. There is another variation in
the CASE expression which looks as below:
Use
Pubs
Go
Select
au_lName, au_FName,
CASE WHEN
contract = 1
Then 'Under Contract'
Else 'Not Under Contract'
End
[Contract]
from
authors
The output is the same in both the cases.
|