7.8 CASE

Use the @CASE function to select a value depending on a series of value tests. There is no limit to the number of cases you can test with @CASE. If the number of cases is large, list the most frequently encountered conditions first for the best performance.

For this function, Oracle GoldenGate supports the use of an escape sequence to represent characters in a string column in Unicode or in the native character encoding of the Microsoft Windows, UNIX, and Linux operating systems. The target column must be a SQL Unicode data type if any argument is supplied as Unicode.

This function does not support NCHAR or NVARCHAR data types.

Syntax

@CASE (value, test_value1, test_result1
[, test_value2, test_result2] [, ...]
[, default_result]

value

A value to test, for example, a column name. Enclose literals within single quote marks.

test_value

A valid result for value. Enclose literals within single quote marks.

test_result

A value to return based on the value of test_value. Enclose literals within single quote marks.

default_result

A default value to return if value results in none of the test_value values. Enclose literals within single quote marks.

Examples

Example 1   

The following returns A car if PRODUCT_CODE is CAR and A truck if PRODUCT_CODE is TRUCK. If PRODUCT_CODE fits neither of the first two cases, a FIELD_MISSING indication is returned because a default value was not specified.

@CASE (PRODUCT_CODE, 'CAR', 'A car', 'TRUCK', 'A truck')

Example 2   

The following is similar to the previous example, except that it provides for a default value. If PRODUCT_CODE is neither CAR nor TRUCK, the function returns A vehicle.

@CASE (PRODUCT_CODE, 'CAR', 'A car', 'TRUCK', 'A truck', 'A vehicle')