@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 thetest_value
values. Enclose literals within single quote marks.
Examples
- Example 1
-
The following returns
A car
ifPRODUCT_CODE
isCAR
andA truck
ifPRODUCT_CODE
isTRUCK
. IfPRODUCT_CODE
fits neither of the first two cases, aFIELD_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 neitherCAR
norTRUCK
, the function returnsA vehicle
.@CASE (PRODUCT_CODE, 'CAR', 'A car', 'TRUCK', 'A truck', 'A vehicle')