mssql-iif mssql-if case mssql-whn

SELECT sum(CASE trants_type when 'Usage' THEN qty ELSE 0 END)  FROM [item inventory]
select * from  [item inventory]



http://stackoverflow.com/questions/63447/how-do-you-perform-an-if-then-in-an-sql-select


select trants_type, location ,[product group code],[item no 2], [ITEM NO],
sum(CASE trants_type when 'On Hand' THEN qty ELSE 0 END)as onhand ,
sum(CASE trants_type when 'Sales' THEN qty ELSE 0 END)as SO,
sum(CASE trants_type
when 'Sales' THEN qty
when 'On Hand' THEN -qty
when 'Transfer Out'THEN -qty
else 0
END)as netAvail,
sum(CASE trants_type when 'Transfer Out' THEN qty ELSE 0 END)as TransferOut,
sum(CASE trants_type when 'Transfer' THEN qty ELSE 0 END)as TransferOrer,
sum(CASE trants_type when 'Purchase-In-Transit' THEN qty ELSE 0 END)as InTransit,
sum(CASE trants_type when 'Transfer-In-Transit' THEN qty ELSE 0 END)as Transfer,
sum(CASE trants_type when 'Purchase' THEN qty when 'Purchase-In-Transit' THEN qty ELSE 0 END)as OnOrder
from  [item inventory]
group by trants_type, location,[item no 2],[ITEM NO], [product group code]

SELECT CAST(
             CASE 
                  WHEN Obsolete = 'N' or InStock = 'Y' 
                     THEN 1 
                  ELSE 0 
             END AS bit) as Salable, * 
FROM Product

You only need to do the CAST if you want the result as a boolean value, if you are happy with an int, this works:

SELECT CASE 
            WHEN Obsolete = 'N' or InStock = 'Y' 
               THEN 1 
               ELSE 0 
       END as Salable, * 
FROM Product

CASE statements can be embedded in other CASE statements and even included in aggregates.

SQL Server Denali adds the IIF statement which is also available in access: (pointed out by Martin Smith)

SELECT IIF(Obsolete = 'N' or InStock = 'Y', 1, 0) as Selable, * from Product