Sunday, June 01, 2014

MS SQL Server iff function vs "case when"

In SQL server, people sometimes use iff function, which is also an Excel function, to assign different values based on a condition. For example, the following query returns 1 if the amt is less than 50, and 0 otherwise.

select id, iif(amt <50, 1,0) as small_amt from tbl_transaction
However, using iff function is not the best practice as iff is not a standard SQL function. The above query only runs successfully on SQL Server. To make the query work for any relational database, such as Oracle, it is better to use SQL standard "case when" statement as shown below.
select id, case when amt<50 then 1 else 0 end small_amt from tbl_transacton
The above query will run successfully on both Oracle and SQL Server databases as they support SQL standard.

No comments: