IF statement syntax in SQL script view
I need to include a "IF" condition in the "SELECT" section of my SQL script view.
I tried the following syntax's but I get the error 'Incorrect SQL syntax near 'IF'
1. IF(Revenue <> '0' AND Quantity <> '0', Revenue/Quantity, '0') AS Gross Price
2. IF(Revenue != '0' AND Quantity != '0', Revenue/Quantity, '0') AS Gross Price
3. IF(Revenue <> '0' AND Quantity <> '0' THEN Revenue/Quantity ELSE '0' END) AS Gross Price
4. IF(Revenue != '0' AND Quantity != '0' THEN Revenue/Quantity ELSE '0' END) AS Gross Price
My final SQL would read like follows:
SELECT field1, field2, IF(......) AS field3
Can anybody please help with the correct IF statement syntax to be used in the SQL script based view?
Sreehari V Pillai replied
You can not use IF along with SELECT statement. Note that, you can achieve most of boolean logics with CASE statement syntax as noted by @Chandra Sekhar . In select, you are applying it over a column and your logic will be executed as many as times the count of result set rows. Hence , righting an imperative logic is not well appreciated. Still, if you want to do the same, create a calculation view and use intermediate calculated columns to achieve what you are expecting .