DB2-Raise Error Function

The RAISE_ERROR function can be used to signal application program and data problems.

One situation where RAISE_ERROR may prove useful is in a CASE statement.

SELECT EMPNO,
  CASE WHEN SEX = ‘M’ THEN ‘MALE ‘
       WHEN SEX = ‘F’ THEN ‘FEMALE’
ELSE RAISE_ERROR(’70SX1′, ‘INVALID DATA, SEX IS NEITHER F NOR M.’)
   END
   FROM DSN71010.EMP;

In Raise Error function, the first function is SQLSTATE. Second function is error decision.

Rules for SQLSTATE:

The value must be a character string of exactly five characters in length

  • Add a note hereOnly the characters '0' through '9' and upper case 'A' through 'Z' may be used.

  • Add a note hereThe first two characters cannot be '00', '01', or '02'.

  • Add a note hereIf the first character is '0' through '6' or 'A' through 'H', the last three characters must start with a letter from 'I' through 'Z'.

  • Add a note hereIf the first character is '7', '8', '9', or 'I' though 'Z', the last three characters can be any valid character.

Advertisements

Author: Srini

Experienced software developer. Skills in Development, Coding, Testing and Debugging. Good Data analytic skills (Data Warehousing and BI). Also skills in Mainframe.