IF THEN statements in SQL
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 2 of 2

Thread: IF THEN statements in SQL

  1. #1
    Join Date
    Jan 2002
    Posts
    15
    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

  2. #2
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width