raise_application_error in db2 functions
I am trying to use raise_application_error within a db2 function like below.
When I try to create that function db2 tells me the following
An unexpected token "raise_application_error" was found following "id is null then ". Expected tokens may include: "RETURN". LINE
Is it allowed to use it in a function or is it meant only for stored procedures?
What is a valid substitute for functions?
create or replace function get_stuff(id integer) returns varchar(10) language sql reads sql data begin declare toreturn varchar(10); if id Is null then raise_application_error(-20000,'Id Missing) end if; --set (toreturn)=select .../// return toreturn; end@
If you are writing a IBM SQL PL you should define the text for a signal
SIGNAL SQLSTATE VALUE '20000' SET MESSAGE_TEXT = 'Id Missing';
If you qre writing a PL/SQL in DB2, you should be sure that the compatibility vector is set to ORA : http://publib.boulder.ibm.com/infocenter/db2luw/v10r1/topic/com.ibm.db2.luw.apdv.porting.doc/doc/r0052867.html
The syntax error you are getting is a result of a missing apostrophe to terminate the string 'Id Missing'.
However, see AngocA's answer for the proper way to raise the condition. His syntax is portable across DB2 platforms.