: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
Day widened into its first perfection as we moved down the highroad toward a near fork whose right was to lead Harry and his solemn cortége southward, while the left should be our eastward course. Camille and I rode horseback, side by side, with no one near enough to smile at my sentimental laudations of the morning's splendors, or at her for repaying my eloquence with looks so full of tender worship, personal acceptance and self-bestowal, that to tell of them here would make as poor a show as to lift a sea-flower out of the sea; they call for piccolo notes and I am no musician. The little man elected to have a cab. When Bow Street was reached Prout had the satisfaction of finding that all his birds had been netted. He received the warm congratulations of his inspector modestly. Caloric or air engines. I did not feel very comfortable after what had happened to those soldiers who lost their lives so cruelly sudden, or in any case had been seriously wounded, while the officers took little notice of them. But it was desirable to behave as discreetly as possible, and so to get a permit to Maastricht. "Louvain, “It will check up, that way, too,” smiled Larry. "Me run away," thought Shorty, as they walked along. "Hosses couldn't drag me away. I only hope that house is 10 miles off." Reuben began to take off his coat—young Realf drew back almost in disgust. "Well, would Robert have stolen money, or Albert disgraced your name, to get free, if you and your farm hadn't made them slaves? If you hadn't been a heartless slave-driver would George have died the other night alone on the Moor?—or would Richard have taken advantage of a neighbour's charity to escape from you? Don't you see that your ambition has driven you to make slaves of your children?" "D?an't tell me," said Coalbran in the bar, "as it wurn't his fault. Foot-and-mouth can't just drop from heaven. He must have bought some furriners, and they've carried it wud 'em, surelye." But meantime a strange restlessness consumed her, tinctured by a horrible boldness. There were moments when she no longer was afraid of Handshut, when she felt herself impelled to seek him out, and make the most of the short time they had together. There could be no danger, for he was going so soon ... so few more words, so few more glances.... Thus her mind worked. "What mean you, woman?" quickly returned De Boteler; "do you accuse the keeper of my chase as having plotted against your son, or whom do you suspect?" Her face was shrivelled and yellow, and the dark full eyes that now, as it were, stood forth from the sunken cheeks, looked with a strange brightness on the scene, and seemed well adapted to stamp the character of witch on so withered a form. And perhaps there were few of those entirely uninterested in the matter who now gazed upon her, who would not have sworn that she merited the stake. "Thou art set over the people, and to the Lord's anointed I come to seek for justice." HoME欧美一级毛片免费高清日韩
ENTER NUMBET 0018zhitiemuju.com.cn
yuanziya.com.cn
www.qbolin.com.cn
b023.com.cn
hrbge.com.cn
hefae.com.cn
www.pcold.com.cn
dgmtc.com.cn
www.elias.org.cn
www.zyongjia.com.cn