SQL Reference

RAISE_ERROR

>>-RAISE_ERROR--(--sqlstate--,--diagnostic-string--)-----------><
 

The schema is SYSIBM.

The RAISE_ERROR function causes the statement that includes the function to return an error with the specified SQLSTATE, SQLCODE -438 and diagnostic-string. The RAISE_ERROR function always returns NULL with an undefined data type.

sqlstate

A character string containing exactly 5 characters. It must be of type CHAR defined with a length of 5 or type VARCHAR defined with a length of 5 or greater. The sqlstate value must follow the rules for application-defined SQLSTATEs as follows:

• 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' since 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 of type CHAR or VARCHAR that returns a character string of up to 70 bytes that describes the error condition. If the string is longer than 70 bytes, it will be truncated.

In order to use this function in a context where Rules for Result Data Types do not apply (such as alone in a select list), 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