on 08-19-2014 12:58 PM
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?
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
User | Count |
---|---|
101 | |
13 | |
13 | |
11 | |
11 | |
7 | |
6 | |
5 | |
4 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.