IF statement syntax in SQL script view

[Former Member]

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

FROM table1

Can anybody please help with the correct IF statement syntax to be used in the SQL script based view?

[sreehari_vpillai · Accepted solution]

Lakshmi,

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 . 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 .

Sreehari

[Former Member · Accepted solution]

Thank you Sreehari, Chandra and Henry for the responses.

The SQL view is built on top of a graphical view, so I will write a calculated column in that and try to use it in my SQL view.