SQL Server 2005 query that uses the IIF with style date bool

Question

Suppose I have TABLE with the following fields: Quantity [number] Price [Number] Returns [bool] If Returns = true then - ([quantity]*[Price])// negative returns If Returns = false then ([quantity]*[Price]) // positive returns Question using SQL Server 2005: Inquiry following his error was in command IIF: [CODE] SELECT quantity Price Returns IIF ([Returns] = 0 ([quantity]*[Price]) (-1)*([quantity]*[Price])) As Total FROM TABLE; [/CODE] SQL Syntax Errors Encountered: The following errors were encountered while parsing the contents of the SQL pane: Error in list of function arguments: '=' not recognized. Unable to parse query text.

Vinay Singh · Answer

Hi Try the query. SELECT quantity Price Returns CASE Returns WHEN 0 THEN ([quantity]*[Price]) ELSE ((-1)*([quantity]*[Price])) END As Total FROM TABLE;

Nitin Sontakke · Answer

Hello The IIF is not a valid SQL Server function. You will need to use case when... instead. As demonstrated below: SELECT [quantity] [Price] [Returns] case when [Returns] = 0 then [quantity] * [Price] else (-1)*([quantity]*[Price]) end as [Total] FROM TABLE; However if your aim is just return positive values then same can be rewritten as follows: SELECT [quantity] [Price] [Returns] [quantity] * [Price] * (case when [Returns] = 0 then 1 else -1 end) as [Total] FROM TABLE; Hope it helps!