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_transactionHowever, 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_transactonThe above query will run successfully on both Oracle and SQL Server databases as they support SQL standard.
No comments:
Post a Comment