Condition Expressions

Data that meets the requirements specified by conditional expressions are filtered during SQL statement execution.

Conditional expressions include the following types:

• CASE

  CASE expressions are similar to the CASE statements in other coding languages.

  Figure 1 shows the syntax of a CASE expression.

  Figure 1 case::=
  

  A CASE clause can be used in a valid expression. condition is an expression that returns a value of Boolean type.

  • If the result is true, the result of the CASE expression is the required result.
  • If the result is false, the following WHEN or ELSE clauses are processed in the same way.
  • If every WHEN condition is false, the result of the expression is the result of the ELSE clause. If the ELSE clause is omitted and has no match condition, the result is NULL.
  • Operations on XML data are supported.

  For example:

  1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21

  gaussdb=# CREATE TABLE case_when_t1(CW_COL1 INT) DISTRIBUTE BY HASH (CW_COL1); gaussdb=# INSERT INTO case_when_t1 VALUES (1), (2), (3); gaussdb=# SELECT * FROM case_when_t1; a --- 1 2 3 (3 rows) gaussdb=# SELECT CW_COL1, CASE WHEN CW_COL1=1 THEN 'one' WHEN CW_COL1=2 THEN 'two' ELSE 'other' END FROM case_when_t1 ORDER BY 1; cw_col1 | case ---------+------- 1 | one 2 | two 3 | other (3 rows) gaussdb=# DROP TABLE case_when_t1;

• DECODE

  Figure 2 shows the syntax of a DECODE expression.

  Figure 2 decode::=
  

  Compare each following compare(n) with base_expr, value(n) is returned if a compare(n) matches the base_expr expression. If base_expr does not match each compare(n), the default value is returned.

  Operations on XML data are supported.

  Conditional Expression Functions describes the examples.

  1 2 3 4 5

  gaussdb=# SELECT DECODE('A','A',1,'B',2,0); case ------ 1 (1 row)

• COALESCE

  Figure 3 shows the syntax of a COALESCE expression.

  Figure 3 coalesce::=
  

  COALESCE returns its first not-NULL value. If all the parameters are NULL, COALESCE will return NULL. This value is replaced by the default value when data is displayed. Like a CASE expression, COALESCE only evaluates the parameters that are needed to determine the result. That is, parameters to the right of the first not-NULL parameter are not evaluated.

  Operations on XML data are supported.

  Example:

  1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17

  gaussdb=# CREATE TABLE c_tabl(description varchar(10), short_description varchar(10), last_value varchar(10)) DISTRIBUTE BY HASH (last_value); gaussdb=# INSERT INTO c_tabl VALUES('abc', 'efg', '123'); gaussdb=# INSERT INTO c_tabl VALUES(NULL, 'efg', '123'); gaussdb=# INSERT INTO c_tabl VALUES(NULL, NULL, '123'); gaussdb=# SELECT description, short_description, last_value, COALESCE(description, short_description, last_value) FROM c_tabl ORDER BY 1, 2, 3, 4; description | short_description | last_value | coalesce -------------+-------------------+------------+---------- abc | efg | 123 | abc | efg | 123 | efg | | 123 | 123 (3 rows) gaussdb=# DROP TABLE c_tabl;

  If description is not NULL, the value of description is returned. Otherwise, parameter short_description is calculated. If short_description is not NULL, the value of short_description is returned. Otherwise, parameter last_value is calculated. If last_value is not NULL, the value of last_value is returned. Otherwise, none is returned.

  1 2 3 4 5

  gaussdb=# SELECT COALESCE(NULL,'Hello World'); coalesce --------------- Hello World (1 row)

• NULLIF

  Figure 4 shows the syntax of a NULLIF expression.

  Figure 4 nullif::=
  

  Only if value1 is equal to value2 can NULLIF return the NULL value. Otherwise, value1 is returned. Operations on XML data are supported.

  Example:

  1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16

  gaussdb=# CREATE TABLE null_if_t1 ( NI_VALUE1 VARCHAR(10), NI_VALUE2 VARCHAR(10) )DISTRIBUTE BY HASH (NI_VALUE1); gaussdb=# INSERT INTO null_if_t1 VALUES('abc', 'abc'); gaussdb=# INSERT INTO null_if_t1 VALUES('abc', 'efg'); gaussdb=# SELECT NI_VALUE1, NI_VALUE2, NULLIF(NI_VALUE1, NI_VALUE2) FROM null_if_t1 ORDER BY 1, 2, 3; ni_value1 | ni_value2 | nullif -----------+-----------+-------- abc | abc | abc | efg | abc (2 rows) gaussdb=# DROP TABLE null_if_t1;

  If value1 is equal to value2, NULL is returned. Otherwise, value1 is returned.

  1 2 3 4 5

  gaussdb=# SELECT NULLIF('Hello','Hello World'); nullif -------- Hello (1 row)

• GREATEST (maximum value) and LEAST (minimum value)

  Figure 5 shows the syntax of a GREATEST expression.

  Figure 5 greatest::=
  

  You can select the maximum value from any numerical expression list. Operations on XML data are supported.

  1 2 3 4 5

  gaussdb=# SELECT greatest(9000,155555,2.01); greatest ---------- 155555 (1 row)

  Figure 6 shows the syntax of a LEAST expression.

  Figure 6 least::=
  

  You can select the minimum value from any numerical expression list.

  Each of the preceding numeric expressions can be converted into a common data type, which will be the data type of the result.

  The NULL values in the list will be ignored. The result is NULL only if the results of all expressions are NULL.

  Operations on XML data are supported.

  1 2 3 4 5

  gaussdb=# SELECT least(9000,2); least ------- 2 (1 row)

  Conditional Expression Functions describes the examples.

• NVL

  Figure 7 shows the syntax of an NVL expression.

  Figure 7 nvl::=
  

  If the value of value1 is NULL, value2 is returned. Otherwise, value1 is returned. Operations on XML data are supported.

  Example:

  1 2 3 4 5

  gaussdb=# SELECT nvl(null,1); nvl ----- 1 (1 row)

  1 2 3 4 5

  gaussdb=# SELECT nvl ('Hello World',1); nvl --------------- Hello World (1 row)