DECODE(x,y(i),...,z) is a special function that decodes expressions in accordance with their values.
x |
check_expression |
Expression (expression) for which a comparison is to be carried out with the values in y(i) |
y(i) |
search_and_result_spec |
<search_and_result_spec> ::= <search_expression>, <result_expression> (y(i)=u(i),v(i), i=1,...) Combination of the comparison value u(i) and the value v(i) that is to replace this comparison value |
z |
default_expression |
Optional default value (LONG value is permissible) |
u(i) |
search_expression |
Comparison value that is to be replaced by v(i) if it matches x |
v(i) |
result_expression |
Value that is to replace the value u(i), if necessary (LONG value is permissible) |
The data types of x and u(i) must be comparable. The data types of v(i) and z must be comparable. The data types of u(i) and v(i) do not have to be comparable.
DECODE compares the values of x with the values u(i) consecutively. If a match is found, the result of DECODE is the value v(i) in the combination y(i)=u(i),v(i).
A match is exists if x and u(i) are NULL values. The comparison of the special NULL value with any other value never results in a match.
If a match is not found, DECODE supplies the result of z. If z is not specified, the NULL value is the result of DECODE.
SELECT hno, price, DECODE (type,
'single', 1, 'double', 2, 'suite', 3) room_code
FROM room
WHERE hno IN (40,50,60)
The room type identifiers are to be replaced in the output by an identifier declared in the DECODE function.
HNO |
PRICE |
ROOM_CODE |
40 |
140 |
2 |
40 |
85 |
1 |
50 |
180 |
2 |
50 |
105 |
1 |
50 |
500 |
3 |
60 |
200 |
2 |
60 |
120 |
1 |
60 |
500 |
3 |