NexusDB Guide > SQL Reference > Value Expressions

NexusDB V3 Manual

Case Expressions

NexusDB Manual V3 > SQL Reference > Value Expressions

Specify a conditional value.

Syntax

<case expression> ::=

<case abbreviation> ::=

NULLIF ( <value expression> , <value expression> )

COALESCE ( <value expression> { , <value expression> }... )

<case specification> ::=

CASE <case operand> <simple when clause>... [ <else clause> ] END

CASE <searched when clause>... [ <else clause> ] END

<result> ::=

NULL

Usage

The CASE expression is similar in concept to the CASE statement found in some programming languages. It is used in SQL to provide a conditional value, and can be specified anywhere a value expression is allowed.

Notes

§	The searched CASE expression is the main syntactical version, and the most flexible one, since a search condition is specified for each individual WHEN clause.

§	The simple CASE expression is a handy shorthand that eliminates the need for repeating the same value in each WHEN clause when comparing the same value against different conditions.

§	The NULLIF abbreviation compares two values and returns null if the values are equal, else returning the first value specified. This is shorthand syntax for:

CASE WHEN value1 = value2 THEN NULL ELSE value1 END

§	The COALESCE abbreviation returns the first value in a list of comparable values that is not null, or null if all values in the list are the null value. This is shorthand syntax for:

CASE

WHEN value1 IS NOT NULL THEN value1

WHEN value2 IS NOT NULL THEN value2

WHEN valuen IS NOT NULL THEN valuen

ELSE NULL

END

§	If the optional ELSE clause is omitted, then ELSE NULL is implicit.

§	The resulting data type of a CASE expression is determined according to the rules for Result data types of aggregations.

Tip: Use COALESCE in arithmetic expressions to avoid calculations on a null value.

Examples

1)	The following example uses a searched CASE expression to determine a conditional value for the gender_description column:

SELECT

studentID,

studentName,

CASE

WHEN gender = 'F' THEN 'Female'

WHEN gender = 'M' THEN 'Male'

ELSE 'Not available'

END AS gender_description

FROM students

2)	The following example uses a simple CASE expression to determine a conditional value for the gender_description column:

SELECT

studentID,

studentName,

CASE gender

WHEN 'F' THEN 'Female'

WHEN 'M' THEN 'Male'

ELSE 'Not available'

END AS gender_description

FROM students

3)	The following example uses the NULLIF function to display a null in the rows where the orderTotal column has a value of 0:

SELECT

orderID,

NULLIF( orderTotal, 0 ) AS orderTotal

FROM orders

ORDER BY orderID

4)	The following example uses the COALESCE function to avoid calculations on potential null values:

SELECT

orderID,

COALESCE( orderTotal, 0 ) - COALESCE( amountPaid, 0 ) AS "Amount Due"

FROM orders

ORDER BY orderID

Conformance

SQL:2003 standard

Core SQL

nexus_logo