Condition Expressions

Data that meets the requirements specified by condition expressions is filtered during SQL statement execution.

Condition expressions include the following types:

• CASE

  CASE expressions are similar to CASE statements in other programming 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 clause is processed in the same way.
  • If no WHEN condition is true, the result of the expression is result of the ELSE clause. If the ELSE clause is omitted and no match is found, the result is NULL.
  • Operations on XML data are supported.

  Example:

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

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

• 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 no match occurs, default is returned.

  Operations on XML data are supported.

  For details about examples, see "SQL Reference > Functions and Operators > Condition Expression Functions" in Developer Guide.

  1 2 3 4 5

  m_db=# 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, NULL is returned. It is often used to replace NULL with the default value. 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 non-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 18

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

  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 final_value is calculated. If final_value is not NULL, the value of final_value is returned. Otherwise, none is returned.

  1 2 3 4 5

  m_db=# 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 the value of value1 is equal to that of value2 can NULLIF return NULL. Otherwise, the value of 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 17 18

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

  If the value of value1 is equal to that of value2, NULL is returned. Otherwise, the value of value1 is returned.

  1 2 3 4 5

  m_db=# 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::=
  

  Select the maximum value from any numerical expression list. Operations on XML data are supported.

  1 2 3 4 5

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

  Figure 6 shows the syntax of a LEAST expression.

  Figure 6 least::=
  

  Select the minimum value from any numerical expression list.

  Each of the preceding numerical 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 omitted. The result is NULL only if the results of all expressions are NULL.

  Operations on XML data are supported.

  1 2 3 4 5

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

  For details about examples, see "SQL Reference > Functions and Operators > Condition Expression Functions" in Developer Guide.