Hi,
I am trying to create a function which returns a scalar data. My function tries to insert a row in a table as well.

/**

CREATE function getManufacturerId
(
manufacturer VARCHAR(128)
)
RETURNS BIGINT
Language sql
modifies sql data
BEGIN ATOMIC

declare manufacturer_row bigint;
set manufacturer_row = (select manufacturer_id from manufacturer where manufacturer_name = manufacturer);
IF(manufacturer_row IS NULL ) THEN

set manufacturer_row = nextval for manufacturer_seq ;
INSERT INTO manufacturer VALUES(manufacturer_row, manufacturer);

END IF;
return manufacturer_row;
END@
*/

I am getting following error.

DB21034E The command was processed as an SQL statement because it was not a valid Command Line Processor command. During SQL processing it returned:
SQL0628N Multiple or conflicting keywords involving the "MODIFIES SQL DATA" clause are present. LINE NUMBER=18. SQLSTATE=42613

After googling for a while i found out that only table functions can declare "modifies sql data". This raises the question whether i can create a table function to get only one value from the function and use the return value in "call procedure statement".


In my procedure i have to use the above function

call getManufacturerId(manufacturer)

And the above statement will return me the result set if i use table function, how can i use that result set in procedure to get value.

Similarly instead of using function which has plenty of restrictions, if i create procedure can i get get one value by calling a procedure from a procedure.

I hope I am able to explain my problem, if not please inform me i will try to explain again.

Any hint to solve above problem will be highly appreciated.

Thanks!

There's a hint for you in the manual: "MODIFIES SQL DATA valid only if RETURNS specifies a table (TABLE column-list)"

Hi,
Thanks for replying.

Well as i said i my email earlier that i came to know that only table function can have "modifies sql data".

My requirement is to use this function (table or scalar) in a procedure where it gets a value (may be a row if i am able to implement that) after calling the function.

Can somebody tell me how can i use a table function to get a row and use it in a procedure? Does DB2 provides method like fetch one row etc. or means to extract data from the result set?

I am porting my database from firebird (derived from interbase) to db2 and most of the database provide the functionality like

Execute procedure (param) returning values retVal;

or function provide this capability by which we can get a value without restrictions.

Any help in solving the above problem will be highly appreciated.

One solution which i can implement is to create a procedure with out parameter.

But why to implement procedure when the same should be done using function?

Other solution which i found out from other forum is to create a function table which returns like

...Returns table(int)
...Return values(1)

This will give me only one value.