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.FUNCTION1( 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.PATIENT_VISIT_INFO_VIEW
AS WHERE PATIENT_VISIT_INFO_VIEW.PATIENT_ID = FUNCTION1.patient_id;

RETURN xxxxx;
END

Really appreciate your help.

Thanks
Mahesh

[Brian Tkatch]

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.FUNCTION1( 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.PATIENT_VISIT_INFO_VIEW
AS WHERE PATIENT_VISIT_INFO_VIEW.PATIENT_ID = FUNCTION1.patient_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]

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.FUNCTION1( 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.PATIENT_VISIT_INFO_VIEW
AS WHERE PATIENT_VISIT_INFO_VIEW.PATIENT_ID = FUNCTION1.patient_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]

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/Fo...Forum2006.html

[Mahesh S]

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

I have now got the UDF working.

Regards