:meta-keywords: cubrid coalesce, cubrid decode, cubrid greatest, cubrid if, cubrid ifnull, cubrid nvl, cubrid isnull, cubrid least, cubrid nullif, cubrid nlv2
:tocdepth: 3
********************
Comparison Functions
********************
.. contents::
COALESCE
========
.. function:: COALESCE(expression [, expression ] ...)
The **COALESCE** function has more than one expression as an argument. If the first argument is non-**NULL**, the corresponding value is returned if it is **NULL**, the second argument is returned. If all expressions which have an argument are **NULL**, **NULL** is returned. Therefore, this function is generally used to replace **NULL** with other default value.
:param expression: Specifies more than one expression. Their types must be comparable each other.
:rtype: determined with the type of the arguments
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**
* **BIT** < **VARBIT**
* **SHORT** < **INT** < **BIGINT** < **NUMERIC** < **FLOAT** < **DOUBLE**
* **DATE** < **TIMESTAMP** < **DATETIME**
For example, if a type of a is **INT**, b, **BIGINT**, c, **SHORT**, and d, **FLOAT**, then **COALESCE** (a, b, c, d) returns a **FLOAT** type. If a type of a is **INTEGER**, b, **DOULBE** , c, **FLOAT**, and d, **TIMESTAMP**, then **COALESCE** (a, b, c, d) returns a **VARCHAR** type.
**COALESCE** (*a, b*) works the same as the **CASE** expression as follows: ::
CASE WHEN a IS NOT NULL
THEN a
ELSE b
END
.. code-block:: sql
SELECT * FROM case_tbl;
::
a
=============
1
2
3
NULL
.. code-block:: sql
--substituting a default value 10.0000 for a NULL value
SELECT a, COALESCE(a, 10.0000) FROM case_tbl;
::
a coalesce(a, 10.0000)
===================================
1 1.0000
2 2.0000
3 3.0000
NULL 10.0000
DECODE
======
.. function:: DECODE(expression, search, result [, search, result]* [, default])
As well as a **CASE** expression, the **DECODE** function performs the same functionality as the **IF** ... **THEN** ... **ELSE** statement. It compares the *expression* argument with *search* argument, and returns the *result* corresponding to *search* that has the same value. It returns *default* if there is no *search* with the same value, and returns **NULL** if *default* is omitted. An expression argument and a search argument to be comparable should be same or convertible each other. The number of digits after the decimal point is determined to display all significant figures including valid number of all *result*.
:param expression,search: expressions that are comparable with each other
:param result: the value to be returned when matched
:param default: the value to be retuned when no match is found
:rtype: determined with the type of *result* and *default*
**DECODE**\(*a*, *b*, *c*, *d*, *e, f*) has the same meaning as the **CASE** expression below. ::
CASE WHEN a = b THEN c
WHEN a = d THEN e
ELSE f
END
.. code-block:: sql
SELECT * FROM case_tbl;
::
a
=============
1
2
3
NULL
.. code-block:: sql
--Using DECODE function to compare expression and search values one by one
SELECT a, DECODE(a, 1, 'one', 2, 'two', 'other') FROM case_tbl;
::
a decode(a, 1, 'one', 2, 'two', 'other')
===================================
1 'one'
2 'two'
3 'other'
NULL 'other'
.. code-block:: sql
--result types are converted to a single type containing all of significant figures
SELECT a, DECODE(a, 1, 1, 2, 1.2345, 1.234567890) FROM case_tbl;
::
a decode(a, 1, 1, 2, 1.2345, 1.234567890)
===================================
1 1.000000000
2 1.234500000
3 1.234567890
NULL 1.234567890
.. code-block:: sql
--an error occurs when result types are not convertible
SELECT a, DECODE(a, 1, 'one', 2, 'two', 1.2345) FROM case_tbl;
::
ERROR: Cannot coerce 'one' to type double.
GREATEST
========
.. function:: GREATEST(expression [, expression] ...)
The **GREATEST** function compares more than one expression specified as parameters and returns the greatest value. If only one expression has been specified, the expression is returned because there is no expression to be compared with.
Therefore, more than one expression that is specified as parameters must be of the type that can be compared with each other. If the types of the specified parameters are identical, so are the types of the return values; if they are different, the type of the return value becomes a convertible common data type.
That is, the **GREATEST** function compares the values of column 1, column 2 and column 3 in the same row and returns the greatest value while the **MAX** function compares the values of column in all result rows and returns the greatest value.
:param expression: Specifies more than one expression. Their types must be comparable each other. One of the arguments is **NULL**, **NULL** is returned.
:rtype: same as that of the argument
The following example shows how to retrieve the number of every medals and the highest number that Korea won in the *demodb* database.
.. code-block:: sql
SELECT gold, silver , bronze, GREATEST (gold, silver, bronze)
FROM participant
WHERE nation_code = 'KOR';
::
gold silver bronze greatest(gold, silver, bronze)
=======================================================================
9 12 9 12
8 10 10 10
7 15 5 15
12 5 12 12
12 10 11 12
IF
==
.. function:: IF(expression1, expression2, expression3)
The **IF** function returns *expression2* if the value of the arithmetic expression specified as the first parameter is **TRUE**, or *expression3* if the value is **FALSE** or **NULL**. *expression2* and *expression3* which are returned as a result must be the same or of a convertible common type. If one is explicitly **NULL**, the result of the function follows the type of the non-**NULL** parameter.
:param expression1: comparison expression
:param expression2: the value to be returned when *expression1* is true
:param expression3: the value to be returned when *expression1* is not true
:rtype: type of *expression2* or *expression3*
**IF**\(*a*, *b*, *c*) has the same meaning as the **CASE** expression in the following example: ::
CASE WHEN a IS TRUE THEN b
ELSE c
END
.. code-block:: sql
SELECT * FROM case_tbl;
::
a
=============
1
2
3
NULL
.. code-block:: sql
--IF function returns the second expression when the first is TRUE
SELECT a, IF(a=1, 'one', 'other') FROM case_tbl;
::
a if(a=1, 'one', 'other')
===================================
1 'one'
2 'other'
3 'other'
NULL 'other'
.. code-block:: sql
--If function in WHERE clause
SELECT * FROM case_tbl WHERE IF(a=1, 1, 2) = 1;
::
a
=============
1
IFNULL, NVL
===========
.. function:: IFNULL(expr1, expr2)
.. function:: NVL(expr1, expr2)
The **IFNULL** function is working like the **NVL** function; however, only the **NVL** function supports collection type as well. The **IFNULL** function (which has two arguments) returns *expr1* if the value of the first expression is not **NULL** or returns *expr2*, otherwise.
:param expr1: expression
:param expr2: the value to be returned when *expr1* is **NULL**
:rtype: determined with the type of *expr1* and *expr2*
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**
* **BIT** < **VARBIT**
* **SHORT** < **INT** < **BIGINT** < **NUMERIC** < **FLOAT** < **DOUBLE**
* **DATE** < **TIMESTAMP** < **DATETIME**
For example, if a type of a is **INT** and b is **BIGINT**, then **IFNULL** (a, b) returns a **BIGINT** type. If a type of a is **INTEGER** and b is **TIMESTAMP**, then **IFNULL** (a, b) returns a **VARCHAR** type.
**IFNULL**\(*a*, *b*) or **NVL**\(*a*, *b*) has the same meaning as the **CASE** expression below. ::
CASE WHEN a IS NULL THEN b
ELSE a
END
.. code-block:: sql
SELECT * FROM case_tbl;
::
a
=============
1
2
3
NULL
.. code-block:: sql
--returning a specific value when a is NULL
SELECT a, NVL(a, 10.0000) FROM case_tbl;
::
a nvl(a, 10.0000)
===================================
1 1.0000
2 2.0000
3 3.0000
NULL 10.0000
.. code-block:: sql
--IFNULL can be used instead of NVL and return values are converted to the string type
SELECT a, IFNULL(a, 'UNKNOWN') FROM case_tbl;
::
a ifnull(a, 'UNKNOWN')
===================================
1 '1'
2 '2'
3 '3'
NULL 'UNKNOWN'
ISNULL
======
.. function:: ISNULL(expression)
The **ISNULL** function performs a comparison to determine if the result of the expression specified as an argument is **NULL**. The function returns 1 if it is **NULL** or 0 otherwise. You can check if a certain value is **NULL**. This function is working like the **ISNULL** expression.
:param expression: An arithmetic function that has a single-value column, path expression (ex.: *tbl_name.col_name*), constant value is specified.
:rtype: INT
.. code-block:: sql
--Using ISNULL function to select rows with NULL value
SELECT * FROM condition_tbl WHERE ISNULL(salary);
::
id name dept_name salary
======================================================================
7 'Brown ' 'account' NULL
LEAST
=====
.. function:: LEAST(expression [, expression] ...)
The **LEAST** function compares more than one expression specified as parameters and returns the smallest value. If only one expression has been specified, the expression is returned because there is no expression to be compared with.
Therefore, more than one expression that is specified as parameters must be of the type that can be compared with each other. If the types of the specified parameters are identical, so are the types of the return values; if they are different, the type of the return value becomes a convertible common data type.
That is, the **LEAST** function compares the values of column 1, column 2 and column 3 in the same row and returns the smallest value while the :func:`MIN` compares the values of column in all result rows and returns the smallest value.
:param expression: Specifies more than one expression. Their types must be comparable each other. One of the arguments is **NULL**, **NULL** is returned.
:rtype: same as that of the argument
The following example shows how to retrieve the number of every medals and the lowest number that Korea won in the *demodb* database.
.. code-block:: sql
SELECT gold, silver , bronze, LEAST(gold, silver, bronze) FROM participant
WHERE nation_code = 'KOR';
::
gold silver bronze least(gold, silver, bronze)
====================================================================
9 12 9 9
8 10 10 8
7 15 5 5
12 5 12 5
12 10 11 10
NULLIF
======
.. function:: NULLIF(expr1, expr2)
The **NULLIF** function returns **NULL** if the two expressions specified as the parameters are identical, and returns the first parameter value otherwise.
:param expr1: expression to be compared with *expr2*
:param expr2: expression to be compared with *expr1*
:rtype: type of *expr1*
**NULLIF** (*a*, *b*) is the same of the **CASE** expression. ::
CASE
WHEN a = b THEN NULL
ELSE a
END
.. code-block:: sql
SELECT * FROM case_tbl;
::
a
=============
1
2
3
NULL
.. code-block:: sql
--returning NULL value when a is 1
SELECT a, NULLIF(a, 1) FROM case_tbl;
::
a nullif(a, 1)
===========================
1 NULL
2 2
3 3
NULL NULL
.. code-block:: sql
--returning NULL value when arguments are same
SELECT NULLIF (1, 1.000) FROM db_root;
::
nullif(1, 1.000)
======================
NULL
.. code-block:: sql
--returning the first value when arguments are not same
SELECT NULLIF ('A', 'a') FROM db_root;
::
nullif('A', 'a')
======================
'A'
NVL2
====
.. function:: NVL2(expr1, expr2, expr3)
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**.
:param expr1: expression
:param expr2: the value to be returned when *expr1* is not **NULL**
:param expr3: the value to be returned when *expr1* is **NULL**
:rtype: determined with the type of *expr1*, *expr2* and *expr3*
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**
* **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.
.. code-block:: sql
SELECT * FROM case_tbl;
::
a
=============
1
2
3
NULL
.. code-block:: sql
--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
"I can't think of it," replied Frank; "what is it?" Judy Abbott Still the tonga; uphill and down, over the hilly country, with a horizon of dull, low mountains, and the horses worse and worse, impossible to start but by a storm of blows. Towards evening a particularly vicious pair ended by overturning us into a ditch full of liquid mud. The sais alone was completely immersed, and appealed loudly to Rama with shrieks of terror. Abibulla on his part, after making sure that the sahibs and baggage were all safe and sound, took off his shoes, spread his dhoti on the ground, and made the introductory salaams of thanksgiving to the Prophet, while the coolie driver returned thanks to Rama. "Did the girl know her own story?" she asked. "Nonsense—you're coming wud me." "I'm afraid father forgets things. But come in, he's bound to be home to his dinner soon." HoME香港一级高清仑片
ENTER NUMBET 0018poxx.com.cn
www.hbryyy.com.cn
www.jiakapay.com.cn
luxus.net.cn
qxvu.com.cn
www.400tour.com.cn
www.vpgm.com.cn
www.zgjxcm.com.cn
9191191.com.cn
szsd.net.cn