5.13 Decode statement

 < Day Day Up > 



5.13 Decode statement

Oracle DECODE statement can be converted to DB2 CASE statement in two ways:

    DECODE (condition, case1, assign1, case2, assign 2....,default) 

This can be converted to the simple CASE statement:

    CASE condition       WHEN case1 THEN assign 1       WHEN case2 THEN assign 2       ....       ELSE default    END 

Or, to the searched CASE statement:

    CASE       WHEN condition THEN assign 1       WHEN condition THEN assign 2       ....       ELSE default    END 

For example, the Oracle code:

    SELECT AVG(DECODE(Grade, 'A', 1,                             'B', 2,                             'C', 3,                             'D', 4,                             'E', 5))    INTO v_Grade FROM Students    WHERE DEPARTMENT = p_Department AND Course_ID = p_Course_ID; 

This can be converted to DB2 code:

    SELECT AVG(CASE GRADE WHEN 'A' THEN 1                          WHEN 'B' THEN 2                          WHEN 'C' THEN 3                          WHEN 'D' THEN 4                          WHEN 'E' THEN 5                          END) INTO v_Grade    FROM Students    WHERE DEPARTMENT = p_Department      AND Course_ID = p_Course_ID; 



 < Day Day Up > 



Oracle to DB2 UDB Conversion Guide2003
Oracle to DB2 UDB Conversion Guide2003
ISBN: N/A
EAN: N/A
Year: 2004
Pages: 132

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net