Open Source RDBMS - Seamless, Scalable, Stable and Free

한국어 | Login |Register

Versions available for this page: CUBRID 8.3.0 |  CUBRID 8.3.1 |  CUBRID 8.4.0 |  CUBRID 8.4.1 |  CUBRID 8.4.3 |  CUBRID 9.0.0 | 

NVL2 Function

Description

Three parameters are specified for the NVL2 function. The second expression (expr2) is returned if the first expression (expr1) is not NULL; the third expression (expr3) is returned if it is NULL.

Operation is performed by converting the type of every argument into that with the highest priority. If there is an argument whose type cannot be converted, the type of every argument is converted into a VARCHAR type. The following list shows priority of conversion based on input argument type.

  • CHAR < VARCHAR
  • NCHAR < NCHAR VARING
  • BIT < VARBIT
  • SHORT < INT < BIGINT < NUMERIC < FLOAT < DOUBLE
  • DATE < TIMESTAMP < DATETIME

For example, if a type of a is INT, b, BIGINT, and c, SHORT, then NVL2(a, b, c) returns a BIGINT type. If a type of a is INTEGER, b, DOUBLE, and c, TIMESTAMP, then NVL2(a, b, c) returns a VARCHAR type.

Syntax

NVL2( expr1, expr2, expr3 )

 

result :

expr2 | expr3

Example

SELECT * FROM case_tbl;

            a

=============

            1

            2

            3

         NULL

 

--returning a specific value of INT type

SELECT a, NVL2(a, a+1, 10.5678) FROM case_tbl;

            a  nvl2(a, a+1, 10.5678)

====================================

            1                      2

            2                      3

            3                      4

         NULL                     11