By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
443,313 Members | 1,145 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 443,313 IT Pros & Developers. It's quick & easy.

user defined function and executing sql statements

P: n/a
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

Dec 1 '06 #1
Share this Question
Share on Google+
4 Replies


P: n/a
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.

Dec 1 '06 #2

P: n/a
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
Dec 1 '06 #3

P: n/a
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
Dec 1 '06 #4

P: n/a
Thanks a lot for the suggestions.. that helps..

I have now got the UDF working.

Regards

Dec 4 '06 #5

This discussion thread is closed

Replies have been disabled for this discussion.