CASE, COALESCE, NULLIF, and CAST

Expressions › CASE, COALESCE, NULLIF, and CAST

CASE, COALESCE, NULLIF, and CAST

CASE Expressions

Beginning in r11, CA Datacom/DB supports CASE expressions. The value of a CASE expression is the result of the first CASE that evaluates to TRUE. If no CASE evaluates to TRUE, the value is the result of the ELSE. If no ELSE is coded, the value is the NULL value.

Note: If a CASE evaluates to UNKNOWN because of NULL values, the effect is the same as if the CASE evaluated to FALSE, and the next CASE or ELSE is evaluated.

►►─ CASE ─┬─ searched-when-clause ─┬─┬──────────────┬─ END ───────────────────►◄
          └─ simple-when-clause ───┘ └─ ELSEresult ─┘
   ┌──────────────────────────────────┐
►►─▼─ WHENsearch-conditionTHENresult ─┴───────────────────────────────────────►◄
                ┌────────────────────────────┐
►►─ expression ─▼─ WHENexpressionTHENresult ─┴────────────────────────────────►◄
►►─┬─ expression ─┬───────────────────────────────────────────────────────────►◄
   └─ NULL ───────┘

CASE: Begins a CASE expression.
searched-when-clause: Specifies a search-condition and the result when that condition is true.
simple-when-clause: The value of the first expression is compared to each WHEN expression, and the CASE result is the first WHEN result that is TRUE. The data type of the expressions must be compatible.
result: Specifies an expression that follows the THEN and ELSE keywords. There must be at least one result in the CASE expression that is not NULL. All result expressions must be compatible.
search-condition: Specifies a condition that is true, false, or unknown about a row. The search condition cannot contain a subquery.
END: Ends a CASE expression.

Example 1 (Simple WHEN Clause)

Using simple WHEN clauses, this example translates the response codes to a survey:

 SELECT EMPNO, QUESTION,
     CASE RESPONSE_CODE
     WHEN 1 THEN 'Strongly Disagree'
     WHEN 2 THEN 'Somewhat Disagree'
     WHEN 3 THEN 'Neutral'
     WHEN 4 THEN 'Somewhat Agree'
     WHEN 5 THEN 'Strongly Agree'
     ELSE        'No Response'
     END
 FROM SURVEY;

Example 2 (Searched WHEN Clause)

Using searched WHEN clauses, this example translates the response codes to a survey:

 SELECT EMPNO, QUESTION,
     CASE
     WHEN MODE = YN AND RESPONSE_CODE = 1 THEN 'YES'
     WHEN MODE = YN AND RESPONSE_CODE = 2 THEN 'NO '
     ELSE 'NO RESPONSE'
     END
 FROM SURVEY;

COALESCE and NULLIF Expressions

Beginning in r11, CA Datacom/DB supports COALESCE and NULLIF expressions, shortcuts for expressing two frequent uses of the CASE expression.

COALESCE is the ANSI 1999-compatible version of the VALUE scalar function. It returns the first non-NULL argument it is passed.

                  ┌─ , ─────────────────┐
►►─ COALESCE ─ ( ─▼─ result-expression ─┴─ ) ─────────────────────────────────►◄

NULLIF returns NULL if the arguments it is passed are equivalent. Otherwise, it returns the first argument.

►►─ NULLIF ─ (result-expression-1,result-expression-2) ───────────────────────►◄

Coding:

COALESCE(value1, value2)

is the same as coding:

CASE WHEN value1 IS NOT NULL THEN value1 ELSE value2 END

Example

Using COALESCE, select the value of parm, which can be specified at the global, region or individual level:

 SELECT COALESCE(I.PARM, R.PARM, G.PARM)
 FROM GLOBAL G, REGION R, INDIVIDUAL I
 WHERE I.REGION = R.REGION
   AND I.ID_NBR = :HST_ID_NBR

Specifying:

NULLIF(value1, value2)

is the same as specifying:

CASE WHEN value1 = value2 THEN NULL ELSE value1 END

Example

Using NULLIF, substitute NULLs for zeros as follows:

 SELECT EMPNO, NULLIF(COMMISSION, 0) FROM EMPLOYEE

All arguments passed to COALESCE and NULLIF must be of compatible data types.

CAST Expressions

Beginning in r11, CA Datacom/DB supports CAST expressions to give users access to data stored in non-standard formats. Although CAST has a wide range of potential uses, users of CA Datacom Datadictionary REDEFINEs should be particularly interested in the feature, because it enables SQL to access redefined data using the correct data type.

Consider the following example. Assume that a table named "customers" contains legacy data and has a column called "credit_info" that SQL knows as a CHAR[7]. CA Datacom Datadictionary redefines the column as a CHAR[1] containing a credit "grade" of 'A' through 'F', followed by a 6-digit zoned decimal representing a credit limit in whole dollars. The credit limit can now be accessed as a number using the following syntax:

►►─ CAST ─ (<-source-expression>AS <-target-type>WITHOUT CONVERSION) ─────────►◄

In the syntax, source-expression refers to the source value expression, and target-type refers to the target data type.

WITHOUT CONVERSION is a CA Datacom extension that means the source data is already formatted as the target data type and does not need to be converted. Following is an example specification:

CAST(SUBSTR(credit_info,2,6) AS NUMERIC(6,0) WITHOUT CONVERSION)

CAST WITHOUT CONVERSION allows any data type to be cast to any other data type. However, it is your responsibility to ensure that the source data matches the target data type and is not longer than the target. Leading zeroes and trailing blanks are inserted into targets as needed. For example, casting a CHAR(2) containing 1234 hexadecimal to INTEGER without conversion results in 00001234 hexadecimal, and casting an INTEGER containing F1F2F3F4 hexadecimal to CHAR(6) without conversion results in "1234 " (note the trailing spaces), or F1F2F3F44040 hexadecimal.

Following are some examples to help clarify how to specify the target type for CAST WITHOUT CONVERSION:

If your source data is:	The target type should be:
Binary 2 bytes	SMALLINT
Binary 4 bytes	INTEGER
31 zoned decimal digits	NUMERIC(31)
6 packed decimal digits	DECIMAL(6)

Following is an example of the use of CAST in a query where the credit limit is retrieved as a number rather than a character string:

   SELECT name, account_number,
          CAST(SUBSTR(credit_info,2,6) AS NUMERIC(6,0) WITHOUT CONVERSION)
          AS credit_limit
   FROM customers

Following is an example showing a query that uses the credit limit in a mathematic comparison:

   SELECT name, account_number
   FROM customers
   WHERE
   CAST(SUBSTR(credit_info,2,6) AS NUMERIC(6,0) WITHOUT CONVERSION) > 50000

For the matrix (see Numeric Assignments) of implicit (automatic) data type conversions that CA Datacom/DB already supports, the following version of CAST may also be used:

►►─ CAST ─ (<-source-expression>AS <-target-type>) ───────────────────────────►◄

This version of CAST requires that the source value be interpreted first and then stored into the target in converted form. For example, when casting zoned decimal (NUMERIC) to packed decimal (DECIMAL), the source would have to contain a valid zoned decimal value.