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.

[sreehari_vpillai]

Hi Lakshmi,

below is the syntax for IF statement.

IF <bool_expr1> THEN

<then_stmts1>

[

{

ELSEIF <bool_expr2>

THEN <then_stmts2>

}...]

[ELSE <else_stmts3>]

END IF

eg :

BEGIN

DECLARE found INT := 1;

SELECT count(*) INTO found FROM books WHERE isbn = :v_isbn;

IF :found = 0 THEN

INSERT INTO books VALUES (:v_isbn, 'In-Memory Data Management', 1, 1, '2011', 42.75, 'EUR');

ELSE

UPDATE books SET price = 42.75 WHERE isbn =:v_isbn;

END IF;

END;

Sreehari

[Former Member]

Hi Sreehari,

But I will have to include "IF" in the 'SELECT' clause itself just before 'FROM' as follows:

SELECT Revenue, Quantity, IF(...condition...) AS field3

FROM table1

Please let me know if any inputs.

[former_member186082]

Hi Lakshmi,

How about CASE instead of IF? Even I have used CASE for validations in SELECT statement, IF didn't work.

SELECT REVENUE, QUANTITY,CASE WHEN REVENUE <> '0' AND QUANTITY <> '0' THEN REVENUE/QUANTITY ELSE '0' END AS GROSS_PRICE

FROM TABLE1;

This query should work.

Regards,

Chandu.

[sreehari_vpillai]

I don't thing IF is supported here. Would case statement help you ?

select MATNR ,

CASE 

  WHEN "ERSDA" > '20130405' THEN 'Y'

  ELSE 'N'

  END as "TEST"

from "SAPXX"."MARA"; You can change your IF condition this way.

Sree

Message was edited by: Sreehari V Pillai

[Former Member]

Thanks Chandra and Sreehari but for the logic that I need to use, CASE wont work.

I have used CASE too for a different requirement and that works just fine.

If I get to know the exact IF syntax (not sure if it can be used in SELECT though) then I can use that to exactly use the logic I need.

[former_member186082]

Lakshmi, can you tell us your requirement so that we will see if we can help you.

[Former Member]

Lakshmi

im pretty sure you can't use IF inside a SELECT statement as you just said.

The IF statement is to control the flow of logic I.e. To decide which statements to execute in the script.

I Think if you check the syntax for SELECT in the SQL Reference guide you'll find this to be true.

you should be able to get rid of the IF and turn your expression into a 'conditional expression' that uses a logical test and Boolean results in the arithmetic to return the result you need.

Something like

(expression=value*value + expression<>value*X) AS colname

there is no doubt a more elegant way of doing that most likely using CASE but I'm off to meeting and don't have time to check it out at the moment

hope this helps