If you're working on an older or alternate SQL server, the IIF function may not be supported, instead you'll want to use a CASE statement.
Contents:
The Problem
When writing queries in SAP Business One or alternative SQL servers, you may get a syntax error when trying to use the IIF function to add in some logic to your queries.
For instance, let's say that you are trying to use IIF to specify that if the ItemCode from table alias T0, was 'Shipping Charges', then return price before distribution, otherwise, multiple the quantity by average price, and call it TotalLineCost:
IIF(T0."ItemCode" = 'Shipping Charges', T0."PriceBefDi", T0."Quantity" * T4."AvgPrice") As "TotalLineCost"If you tried to run this in SAP Business One or in an older SQL server, a syntax error that states: "SQL Syntax error: incorrect syntax near "=" ... 'User-Defined Values'" would be thrown.
It may be a little bit hard to diagnose what is going on here, as you may assume that IIF would work here just as with other SQL servers.
The Solution
Because the IFF Function is not available on all SQL servers (SAP Business One being one of them), we need a work around solution.
In this scenario, we should look to use a CASE statement, as CASE is portable across all SQL platforms whereas IIF is SQL SERVER 2012+ specific. You can replace the IIF function with a CASE statement, and this solution will function mostly the same.
The IIF statement above can be re-written using a CASE statement as show below:
CASE
WHEN T0."ItemCode" = 'Shipping Charges'
THEN T0."PriceBefDi"
ELSE T0."Quantity" * T4."AvgPrice"
END AS "TotalLineCost"The CASE statement operates just like the IF function in Excel, or the IIF function, but is structured a bit differently.
The CASE statement will go through specified conditions for each WHEN, and when the condition is met, return the value or result held in the THEN line. If no conditions are met, then the value in the ELSE line will be returned.
So, the code above is states that whenever the 'ItemCode' is equal to 'Shipping Charges', then return the price before distribution, otherwise multiply quantity by average price.