SQL RAISE_ERROR scalar function in DB2 for i SQL

SQL RAISE_ERROR scalar function in DB2 for i SQL

RAISE_ERROR

The RAISE_ERROR function return an error with the specified SQLSTATE along with SQLCODE -438 and error condition. The RAISE_ERROR function always returns the null value./p>

Syntax of RAISE_ERROR

RAISE_ERROR(sqlstate, diagnostic-string)

sqlstate is exactly of 5 characters. Rules for sqlstate.

Each character must be either digit '0' through '9' or upper case letters 'A' through 'Z'.

First two character denotes SQLSTATE class and cannot be '00', '01', or '02' because these are not error classes.

If the first two characters i.e. SQLSTATE class starts with the character '0' through '6' or 'A' through 'H', then the last three characters i.e. SQLSTATE subclass must start with a letter in the range 'I' through 'Z'.

If the first two characters i.e. SQLSTATE class starts with the character '7', '8', '9', or 'I' though 'Z', then the last three characters i.e. SQLSTATE subclass can be any of '0' through '9' or 'A through 'Z'.

diagnostic-string is a character string with a data type of CHAR or VARCHAR and a length of up to 70 bytes.

Notes:

Since RAISE_ERROR is return value data type is undefined, therefore it can only be used in a SET host-variable or SQL procedure language assignment statement. To use this function in select statement then we must use a cast specification to give a data type to the null value that is returned.

Example using RAISE_ERROR

SELECT                                         
     CASE WHEN 35 < 16 THEN 'LESSER'           
          ELSE RAISE_ERROR('60002',            
                           'GREATER than 35')  
     END                                       
     FROM sysibm.sysdummy1                     

  Query cannot be run.  See lower level messages.   

DSPJOBLOG on command line

 3 > strsql                                                                 
     Message GREATER than 35 returned from SIGNAL, RESIGNAL, or RAISE_ERROR.
     User-defined function error on member SYSDUMMY1.                       
     Cancel reply received for message CPF503E.                             
     Job 529555/EASYCLASS/QPAD100947 changed by JOBMANAGER.                 
     Job 529555/EASYCLASS/QPAD100947 changed by JOBMANAGER.                 
 3>> dspjoblog                                                              

Take F1 on messages

                        Additional Message Information                        
                                                                              
Message ID . . . . . . :   SQL0438       Severity . . . . . . . :   30        
Message type . . . . . :   Diagnostic                                         
Date sent  . . . . . . :   03/29/24      Time sent  . . . . . . :   11:38:12  
                                                                              
Message . . . . :   Message GREATER than 35 returned from SIGNAL, RESIGNAL, or
  RAISE_ERROR.                                                                
Cause . . . . . :   An application has executed a SIGNAL or RESIGNAL          
  statement, the RAISE_ERROR function has been invoked, or an error was       
  signalled within a MERGE statement. If the application is an SQL procedure, 
  function, trigger, or a compound (dynamic) statement, the SQLSTATE was not  
  handled in the SQL routine.  The message returned is GREATER than 35.       
Recovery  . . . :   See the documentation for the application that issued the 
  SIGNAL or RESIGNAL statement or invoked the RAISE_ERROR function.           

                        Additional Message Information                        
                                                                              
Message ID . . . . . . :   CPF503E       Severity . . . . . . . :   30        
Message type . . . . . :   Diagnostic                                         
Date sent  . . . . . . :   03/29/24      Time sent  . . . . . . :   11:38:12  
                                                                              
Message . . . . :   User-defined function error on member SYSDUMMY1.          
Cause . . . . . :   An error occurred while invoking user-defined function    
  RAISE_ERROR in library QSYS2. The error occurred while invoking the         
  associated external program or service program QSQSSUDF in library QSYS,    
  program entry point or external name RAISE_ERROR, specific name RAISE_ERROR.
  The error occurred on member SYSDUMMY1 file SYSDUMMY1 in library SYSIBM. The
  error code is 1. The error codes and their meanings follow:                 
    1 -- The external program or service program returned SQLSTATE 60002. The 
  text message returned from the program is: GREATER than 35 .                
    2 -- The external program failed before it completed.                     
    3 -- The database timed out waiting for the program to return. The timeout
  value used by the database was 0 minutes and 30 seconds.                    
                                                                       More...