Question Re: IF statement syntax in SQL script view in Technology Q&A

IF statement syntax in SQL script view

Former Member — Tue, 19 Aug 2014 11:58:21 GMT

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?

Re: IF statement syntax in SQL script view

sreehari_vpillai — Tue, 19 Aug 2014 12:11:12 GMT

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

Re: IF statement syntax in SQL script view

Former Member — Tue, 19 Aug 2014 12:19:06 GMT

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.

Re: IF statement syntax in SQL script view

Former Member — Tue, 19 Aug 2014 12:31:48 GMT

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.

Re: IF statement syntax in SQL script view

sreehari_vpillai — Tue, 19 Aug 2014 12:43:40 GMT

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

Re: IF statement syntax in SQL script view

Former Member — Tue, 19 Aug 2014 13:52:27 GMT

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.

Re: IF statement syntax in SQL script view

Former Member — Wed, 20 Aug 2014 05:08:18 GMT

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

Re: IF statement syntax in SQL script view

Former Member — Wed, 20 Aug 2014 07:41:23 GMT

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

Re: IF statement syntax in SQL script view

sreehari_vpillai — Wed, 20 Aug 2014 08:33:00 GMT

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

Re: IF statement syntax in SQL script view

Former Member — Wed, 20 Aug 2014 08:41:05 GMT

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.