Hi,

I'm not sure how to call a db2/udb user defined function. Here's the code for the function:
CREATE FUNCTION ABC.getEmployees( departmentid_in INTEGER )
RETURNS TABLE (EMPLOYEEID INTEGER,DEPARTMENTID INTEGER,LASTNAME VARCHAR(40),FIRSTNAME VARCHAR(40),EMPLOYEECODE VARCHAR(10))
------------------------------------------------------------------------
-- SQL UDF (Table)
------------------------------------------------------------------------
F1: BEGIN ATOMIC
RETURN SELECT *
FROM ABC.EMPLOYEE AS EMPLOYEE
WHERE EMPLOYEE.DEPARTMENTID = getEmployees.departmentid_in;
END

The mapping file:
<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC
"-//Hibernate/Hibernate Mapping DTD 3.0//EN"
"http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">
<hibernate-mapping package="com.abc.test.model">
<!-- <class name="Employee" table="Employee"> -->
<class name="Employee">
<id name="employeeId" column="employeeId">
<generator class="assigned" />
</id>
<many-to-one name="department" column="departmentId" class="Department" cascade="save-update"/>
<property name="lastname" />
<property name="firstname" />
<property name="employeeCode" />
<set name="contactInfo" cascade="save-update">
<key column="employeeId"/>
<one-to-many class="ContactInfo"/>
</set>
</class>
<sql-query name="getEmployees" callable="true">
<return alias="employee" class="Employee" />
{? = call ABC.getEmployees(:departmentId)}
</sql-query>
</hibernate-mapping>

And here is the code:
employeeList = session.getNamedQuery("getEmployees").setParameter("departmentId", departmentId).list();

When running this I get this:
[java] 2007-12-03 09:53:45,634 WARN [org.hibernate.util.JDBCExceptionReporter] - SQL Error: -99999, SQLState: 07001
[java] 2007-12-03 09:53:45,634 ERROR [org.hibernate.util.JDBCExceptionReporter] - [IBM][CLI Driver] CLI0100E Wrong number of parameters. SQLSTATE=07001

If I change the mapping to this:
<sql-query name="getEmployees" callable="true">
<return alias="employee" class="Employee" />
{call ABC.getEmployees(:departmentId)}
</sql-query>

I get this error:
[java] 2007-12-03 09:55:31,164 WARN [org.hibernate.util.JDBCExceptionReporter] - SQL Error: -440, SQLState: 42884
[java] 2007-12-03 09:55:31,164 ERROR [org.hibernate.util.JDBCExceptionReporter] - [IBM][CLI Driver][DB2/6000] SQL0440N No authorized routine named "ABC.GETEMPLOYEES" of type "PROCEDURE" having compatible arguments was found. SQLSTATE=42884

How should I do the mapping / calling code to be able to use the function?

TIA,

ksakke