>>-RAISE_ERROR--(--sqlstate--,--diagnostic-string--)-----------><
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 the null value with an undefined
data type. In a Unicode database, if a supplied argument is a graphic
string, it is first converted to a character string before the function
is executed.
- 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. 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.
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.
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
DB2 Version 9.7 for Linux, UNIX, and Windows