IF THEN statements in SQL

[overcast]

IF TERM1_LINE='3'THEN
SELECT TERM1_LINE
FROM TERM_DATA
WHERE
TERM1_REF='TPLAN' AND
TERM1_LINE='3';
ELSE
SELECT TERM1_LINE
FROM TERM_DATA
WHERE
TERM1_REF='TPLAN' AND
TERM1_LINE='0';
END IF;

Is it possible to do something like this? (obviously not working this way) or do i have to make some kinda function or procedure?

Thanks guys

[pando]

well I dont think so but you can probably have at this Metalink note:

Purpose:
========

The purpose of this article is to explain how to build IIF Functions
in Oracle SQL which can enhance the readability of programmer code.


How to Build an IIF Function in Oracle SQL:
===========================================

The following question has been asked by Oracle users many times:

"Why do neither ORACLE nor the ANSI standard include a conditional
function such as IIF(A,B,C) from some languages like VB and dBase,
or, (A?B:C) from C in a SQL standard?”

Sometimes programmer code becomes filled with dozens of DECODE function
calls. When you try to optimize the code, it gets unreadable. If you
define a personal standard, this problem can be avoided.

Consider the following methods for solving this problem. For example, you
have the following conditional expression:

IIF( a=d or not (e<5 or f>5) , 'True', 'False' )

Resolve this expression using Oracle SQL possibilities.


Method 1: Using DECODE.
-----------------------

Note: DECODE is not supported in PL/SQL. So, this algorithm IS ONLY for
SQL statements. The main idea is to divide your original expression
into primitives, and then substitute them with a DECODE expression from
a next table.

Conditional
Primitives DECODE Expression Alternative
------------ ------------------------------ ----------------------
A A<=B Decode( Sign(A-B), 1, 0, 1 )
A>B Decode( Sign(A-B), 1, 1, 0 )
A>=B Decode( Sign(A-B), -1, 0, 1 )
A=B Decode( A, B, 1, 0 )
A between B and C Decode( Sign(A-B), -1, 0, Decode(Sign(A-C), 1, 0, 1 ))
A is null Decode(A,null,1,0)
A is not null Decode(A,null,0,1)
A in (B1,B2,...,Bn) Decode(A,B1,1,B2,1,...,Bn,1,0)
nor LogA Decode( LogA, 0, 1, 0 ) (1-Sign(LogA))
LogA and LogB LogA * LogB
LogA or LogB LogA + LogB
LogA xor LogB Decode(Sign(LogA),Sign(LogB),0,1) Mod(Sign(LogA),Sign(LogB),2)


Then you have to cover all but the last DECODE:

DECODE( ,0, ,).

Using the above note, the example is transformed into:

Decode( Decode(Sign(a-b),-1,1,0) * Decode(Sign(c-d),-1,0,1)
+ Decode( Decode(Sign(e-5),-1,1,0) + Decode(Sign(f-5),1,1,0),0,1,0), 0,'False', 'True' )

Or, if you want the second and third parameters to be replaced:

DECODE( Sign(), 1,, ).

Decode(Sign( Decode(Sign(a-b),-1,1,0) * Decode(Sign(c-d),-1,0,1)
+ Decode( Decode(Sign(e-5),-1,1,0) + Decode(Sign(f-5),1,1,0),0,1,0) ), 1,'True','False' )


Method 2: Polish notation expression.
-------------------------------------

The main advantage with this method is that it is suitable for both SQL
and PL/SQL. However, it is less readable and less productive than the
first method.

Note: DECODE is built-in.

It is necessary to create a few functions for each data type:


o Comparison function:
--------------------

*A < B for NUMBER, DATE, VARCHAR2
CREATE OR REPLACE FUNCTION Lt( a DATE, b DATE ) RETURN NUMBER IS
BEGIN IF a < b THEN RETURN 1; ELSE RETURN 0; END IF; END Lt;
/
CREATE OR REPLACE FUNCTION Lt( a NUMBER, b NUMBER ) RETURN NUMBER IS
BEGIN IF a < b THEN RETURN 1; ELSE RETURN 0; END IF; END Lt;
/
CREATE OR REPLACE FUNCTION Lt( a VARCHAR2, b VARCHAR2 ) RETURN NUMBER IS
BEGIN IF a < b THEN RETURN 1; ELSE RETURN 0; END IF; END Lt;
....
*Repeat for Lq( A, B ) /* A<=B */, Gt( A, B ) /* A>B */, Gq( A, B ) /* A>=B */,
*Eq(A, B ) /* A=B */, Ne(A, B ) /* A!=B */, Bw(A,B,C) /* B between A and C */
*IsN(A) /* A is null */, IsNN(A) /* A is not null */,


o Logical functions:
------------------

*AND
CREATE OR REPLACE FUNCTION Land( a NUMBER, b NUMBER ) RETURN NUMBER IS
BEGIN IF a * b != 0 THEN RETURN 1; ELSE RETURN 0; END IF; END Land;
/
*OR
CREATE OR REPLACE FUNCTION Lor( a NUMBER, b NUMBER ) RETURN NUMBER IS
BEGIN IF a + b != 0 THEN RETURN 1; ELSE RETURN 0; END IF; END Lor;
/
*XOR
CREATE OR REPLACE FUNCTION Lxor( a NUMBER, b NUMBER ) RETURN NUMBER IS
BEGIN IF a != b THEN RETURN 1; ELSE RETURN 0; END IF; END Lxor;
/
*NOT
CREATE OR REPLACE FUNCTION Lnot( a NUMBER ) RETURN NUMBER IS
BEGIN IF a = 0 THEN RETURN 1; ELSE RETURN 0; END IF; END Lnot;
/


o Total Functions:
----------------

*IIF
CREATE OR REPLACE FUNCTION Iif( l NUMBER, a DATE, b DATE ) RETURN DATE IS
BEGIN IF l=0 THEN RETURN b; ELSE RETURN a; END IF; END Iif;
/
CREATE OR REPLACE FUNCTION Iif( l NUMBER, a NUMBER, b NUMBER ) RETURN NUMBER IS
BEGIN IF l=0 THEN RETURN b; ELSE RETURN a; END IF; END Iif;
/
CREATE OR REPLACE FUNCTION Iif( l NUMBER, a VARCHAR2, b VARCHAR2 ) RETURN VARCHAR2 IS
BEGIN IF l=0 THEN RETURN b; ELSE RETURN a; END IF; END Iif;
/

Look at the example:

Iif( Lor( Land(Lt(a,b), Gq(c,d)), Lnot( Lor(Lt(e,5),Gt(f,5)) ) ), 'True', 'False' )


Method 3: PL/SQL's IIF.
-----------------------

You can create your own IIF functions using the CREATE FUNCTION statement:

CREATE OR REPLACE function IIF(b BOOLEAN,e1 NUMBER,e2 NUMBER) return NUMBER IS
begin IF b THEN return e1; ELSE return e2; END IF; END;
/
CREATE OR REPLACE function IIF(b BOOLEAN,e1 VARCHAR2,e2 VARCHAR2) return VARCHAR2 IS
begin IF b THEN return e1; ELSE return e2; END IF; END;
/
CREATE OR REPLACE function IIF(b BOOLEAN,e1 DATE,e2 DATE) return DATE IS
begin IF b THEN return e1; ELSE return e2; END IF; END;
/

Then your example remains without changes:

IIF( a=d or not (e<5 or f>5) , 'True', 'False' )

Note: This case ONLY works in PL/SQL code.