RAISE_ERROR scalar function

The RAISE_ERROR function causes the statement that includes the function to return an error with the specified SQLSTATE, SQLCODE -438, and diagnostic-string.

Read syntax diagramSkip visual syntax diagramRAISE_ERROR(sqlstate, diagnostic-string)

The schema is SYSIBM.

sqlstate
A character string containing exactly 5 bytes. It must be of type CHAR defined with a length of 5 or type VARCHAR defined with a length of 5 or greater. In a Unicode database, the expression can also return a graphic string. If the returned value is not a character string, it is cast to a character string before the function is evaluated.
The sqlstate value must obey the following rules for application-defined SQLSTATEs:
  • Each character must be from the set of digits (0 through 9) or non-accented upper case letters (A through Z)
  • The SQLSTATE class (first two characters) cannot be 00, 01, or 02 because these are not error classes.
  • If the SQLSTATE class (first two characters) starts with the character 0 through 6 or A through H, then the subclass (last three characters) must start with a letter in the range I through Z.
  • If the SQLSTATE class (first two characters) starts with the character 7, 8, 9 or I though Z, then the subclass (last three characters) can be any of 0 through 9 or A through Z.

If the SQLSTATE does not conform to these rules, an error occurs (SQLSTATE 428B3).

diagnostic-string
An expression that returns a character string that describes the error condition, or a Boolean value. In a Unicode database, the expression can also return a graphic string. If the returned value is not a character string, it is cast to a character string before the function is evaluated. If the string exceeds 70 bytes, it is truncated.

Result

The RAISE_ERROR function always returns the null value with an undefined data type. To use this function in a context where the data type cannot be determined, a cast specification must be used to give the null returned value a data type. A CASE expression is where the RAISE_ERROR function will be most useful.

Example

List employee numbers and education levels as Post Graduate, Graduate and Diploma. If an education level is greater than 20, raise an error. 
   SELECT EMPNO,
     CASE WHEN EDUCLVL < 16 THEN 'Diploma'
          WHEN EDUCLVL < 18 THEN 'Graduate'
          WHEN EDUCLVL < 21 THEN 'Post Graduate'
          ELSE RAISE_ERROR('70001',
            'EDUCLVL has a value greater than 20')
     END
   FROM EMPLOYEE