[오라클]Data Type - DECODE 함수와 ORA-01722

DECODE()함수의 Return 값

Return값 = DECODE(expr, search, result [, search, result ]...  [, default ])
<pre> </pre>

- DECODE() 함수의 Return값은 첫번째 result(3번째 인수)의 Datatype에 따라 달라진다.

- 첫번째 result(3번째 인수)가 null 이라면 Return값의 Datatype은 String이다.

- 첫번째 result(3번째 인수)와 나머지 result나 defaut의 Datatype이 다르면

  "ORA-01722: 수치가 부적합합니다." 에러가 발생한다.

 
------------------------------------------------------------------------------------------
Oracle® Database SQL Reference
10g Release 2 (10.2)
Part Number B14200-02
 
[Purpose]
DECODE compares expr to each search value one by one.
If expr is equal to a search, then Oracle Database returns the corresponding result.
If no match is found, then Oracle returns default.
If default is omitted, then Oracle returns null.
 
The arguments can be any of the numeric types (NUMBER, BINARY_FLOAT, or BINARY_DOUBLE)
or character types.
  - If expr and search are character data,
    then Oracle compares them using nonpadded comparison semantics.
    expr, search, and result can be any of the datatypes CHAR, VARCHAR2, NCHAR, or NVARCHAR2.
    The string returned is of VARCHAR2 datatype and is in the same character set
    as the first result parameter.
  - If the first search-result pair are numeric,
    then Oracle compares all search-result expressions and the first expr to determine
    the argument with the highest numeric precedence, implicitly converts the remaining
    arguments to that datatype, and returns that datatype.
 
The search, result, and default values can be derived from expressions.
Oracle Database uses short-circuit evaluation.
That is, the database evaluates each search value only before comparing it to expr,
rather than evaluating all search values before comparing any of them with expr.
Consequently, Oracle never evaluates a search if a previous search is equal to expr.
Oracle automatically converts expr and each search value to the datatype
of the first search value before comparing.
 
Oracle automatically converts the return value to the same datatype as the first result.
If the first result has the datatype CHAR or if the first result is null,
then Oracle converts the return value to the datatype VARCHAR2.
 
In a DECODE function, Oracle considers two nulls to be equivalent.
If expr is null, then Oracle returns the result of the first search that is also null.
 
The maximum number of components in the DECODE function, including expr,
searches, results, and default, is 255.