| < Day Day Up > |
|
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 > |
|