user defined function and executing sql statements

[Mahesh S]

Hi

I want to write a user defined function which does the following.

1. Take a string value as input parameter
2. Should execute a sql statement: select col1, col2, col3, col4 from
table_name where name = value
3. Do some arithmetic operations on the values returned by the above
sql statement.
4. Return the above arithmetic operation as a float value

I am currently stuck in step 2. I dont know how to obtain the values
from the sql statement executed in step 2.

Any pointers will be very helpful.

Here is the template UDF code:

CREATE FUNCTION HEALTHCAREDB.FU NCTION1( patient_id VARCHAR(36) )
RETURNS INTEGER
F1: BEGIN ATOMIC

/*how do i read the results from within the UDF for the below sql
statement*/

SELECT col1, col2, col3, col4 FROM HEALTHCAREDB.PA TIENT_VISIT_INF O_VIEW
AS WHERE PATIENT_VISIT_I NFO_VIEW.PATIEN T_ID = FUNCTION1.patie nt_id;

RETURN xxxxx;
END

Really appreciate your help.

Thanks
Mahesh

[Brian Tkatch]

Re: user defined function and executing sql statements

Mahesh S wrote:

Hi
>
I want to write a user defined function which does the following.
>
1. Take a string value as input parameter
2. Should execute a sql statement: select col1, col2, col3, col4 from
table_name where name = value
3. Do some arithmetic operations on the values returned by the above
sql statement.
4. Return the above arithmetic operation as a float value
>
I am currently stuck in step 2. I dont know how to obtain the values
from the sql statement executed in step 2.
>
Any pointers will be very helpful.
>
Here is the template UDF code:
>
CREATE FUNCTION HEALTHCAREDB.FU NCTION1( patient_id VARCHAR(36) )
RETURNS INTEGER
F1: BEGIN ATOMIC
>
/*how do i read the results from within the UDF for the below sql
statement*/
>
SELECT col1, col2, col3, col4 FROM HEALTHCAREDB.PA TIENT_VISIT_INF O_VIEW
AS WHERE PATIENT_VISIT_I NFO_VIEW.PATIEN T_ID = FUNCTION1.patie nt_id;
>
RETURN xxxxx;
END
>
Really appreciate your help.
>
Thanks
Mahesh

Look at the documentation for the SELECT INTO syntax. That is how to
get values into a local variable.

SELECT col1, col2 INTO var1, var2....

B.

[Knut Stolze]

Re: user defined function and executing sql statements

Mahesh S wrote:

Hi
>
I want to write a user defined function which does the following.
>
1. Take a string value as input parameter
2. Should execute a sql statement: select col1, col2, col3, col4 from
table_name where name = value
3. Do some arithmetic operations on the values returned by the above
sql statement.
4. Return the above arithmetic operation as a float value
>
I am currently stuck in step 2. I dont know how to obtain the values
from the sql statement executed in step 2.
>
Any pointers will be very helpful.
>
Here is the template UDF code:
>
CREATE FUNCTION HEALTHCAREDB.FU NCTION1( patient_id VARCHAR(36) )
RETURNS INTEGER

This is not a FLOAT or DOUBLE PRECISION...

F1: BEGIN ATOMIC
>
/*how do i read the results from within the UDF for the below sql
statement*/
>
SELECT col1, col2, col3, col4 FROM HEALTHCAREDB.PA TIENT_VISIT_INF O_VIEW
AS WHERE PATIENT_VISIT_I NFO_VIEW.PATIEN T_ID = FUNCTION1.patie nt_id;
>
RETURN xxxxx;
END

Use the original SQL approach and apply your operations and aggregation
directly on the subselect in the routine body, e.g:

CREATE FUNCTION ...
RETURNS INTEGER
RETURN SELECT AVG(value)
FROM ( SELECT group_id, ( AVG(col1) + SUM(col2) ) / MIN(col3)
FROM ...
GROUP BY group_id ) AS t

--
Knut Stolze
DB2 Information Integration Development
IBM Germany

[Serge Rielau]

Re: user defined function and executing sql statements

Ideally try to get to what Knut proposes.
If you do need to do procedural logic you can use a FOR loop:

BEGIN ATOMIC
DECLARE x int default 0;
FOR myrow AS SELECT c1 FROM T DO
SET x = myrow.c1 + x;
END FOR;
RETURN x;
END

If you want to do some serious lifting I recommend you write a stored
procedure and call it from the UDF. This could include processing
dynamic SQL, cursors, or condition handling.

CREATE PROCEDURE p(IN arg INT, OUT res INT)
....

CREATE FUNCTION foo(arg)
BEGIN ATOMIC
DECLARE res INT;
CALL p(arg, res);
RETURN res;
END

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

WAIUG Conference

http://www.iiug.org/waiug/present/Forum2006/Forum2006.html

[Mahesh S]

Re: user defined function and executing sql statements

Thanks a lot for the suggestions.. that helps..

I have now got the UDF working.

Regards