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@

Answers


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.


Need Your Help

Add runtime library search paths to pre built binary

osx path binary package dylib

I'm trying to package a pre built binary into a Mac OS application bundle and I need to change the dylib and framework search paths for the executable. Unfortunately I can't rebuild the binaries at...

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.