Function invocation

[Aborges]

Hi,

I created a function, but I don't know to call this function.

CREATE FUNCTION NAMEEMPLOYEE (DEPTO CHAR(3))
RETURNS TABLE (EMPID INTEGER, NAME VARCHAR(60), PHONE CHAR(8))
LANGUAGE SQL
READS SQL DATA
NO EXTERNAL ACTION
DETERMINISTIC
RETURN
SELECT EMPID, NAME, PHONE
FROM BEST.EMPLOYEES
WHERE DEPTO = BEST.DEPTO

Is my function correct?
I'm running DB2 7.1.

Thanks

Anderson Borges.

[Ian]

Aborges wrote:

Hi,

I created a function, but I don't know to call this function.

CREATE FUNCTION NAMEEMPLOYEE (DEPTO CHAR(3))
RETURNS TABLE (EMPID INTEGER, NAME VARCHAR(60), PHONE CHAR(8))
LANGUAGE SQL
READS SQL DATA
NO EXTERNAL ACTION
DETERMINISTIC
RETURN
SELECT EMPID, NAME, PHONE
FROM BEST.EMPLOYEES
WHERE DEPTO = BEST.DEPTO

Is my function correct?
I'm running DB2 7.1.

You don't say what platform you are on, but typically the
call would look like:

select * from table(nameemployee('D00')) as x

[Serge Rielau]

Ian wrote:

Aborges wrote:

>Hi,

I created a function, but I don't know to call this function.

CREATE FUNCTION NAMEEMPLOYEE (DEPTO CHAR(3))
RETURNS TABLE (EMPID INTEGER, NAME VARCHAR(60), PHONE CHAR(8))
LANGUAGE SQL
READS SQL DATA
NO EXTERNAL ACTION
DETERMINISTIC
RETURN
SELECT EMPID, NAME, PHONE
FROM BEST.EMPLOYEES
WHERE DEPTO = BEST.DEPTO

Is my function correct?
I'm running DB2 7.1.

You don't say what platform you are on, but typically the
call would look like:

select * from table(nameemployee('D00')) as x

Actually there is a trap hidden here:
select * from table(nameemployee(CAST('D00' AS CHAR(3)))) as x

Literals are defined as VARCHAR in the standard (who knows why.,...)
In general I don't recommend defining functions with SMALLINT and CHAR
parameters for that reason...

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

[PJackson]

What would be the purpose/advantage of returning something as a table
(I assume a temp table?) as opposed to a resultset? Have not seen
such before.

thanks

Phil
On May 3, 3:17 pm, Serge Rielau <[e-mail]:

Ian wrote:

Aborges wrote:

Hi,

I created a function, but I don't know to call this function.

CREATE FUNCTION NAMEEMPLOYEE (DEPTO CHAR(3))
RETURNS TABLE (EMPID INTEGER, NAME VARCHAR(60), PHONE CHAR(8))
LANGUAGE SQL
READS SQL DATA
NO EXTERNAL ACTION
DETERMINISTIC
RETURN
SELECT EMPID, NAME, PHONE
FROM BEST.EMPLOYEES
WHERE DEPTO = BEST.DEPTO

Is my function correct?
I'm running DB2 7.1.

You don't say what platform you are on, but typically the
call would look like:

select * from table(nameemployee('D00')) as x

Actually there is a trap hidden here:
select * from table(nameemployee(CAST('D00' AS CHAR(3)))) as x

Literals are defined as VARCHAR in the standard (who knows why.,...)
In general I don't recommend defining functions with SMALLINT and CHAR
parameters for that reason...

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

[Knut Stolze]

PJ******@txfb-ins.com wrote:

What would be the purpose/advantage of returning something as a table
(I assume a temp table?) as opposed to a resultset? Have not seen
such before.

Result sets are the means to communicate things from DB2 to the application.
Tables are the construct used internally in DB2. (Granted, result sets are
available today as well within stored procedures.)

Also, result sets are much more dynamic (# of columns, data types, ...).
This is not very favorable for the optimizer because it can't apply many
optimizations for which the structure of the data is needed. Table
functions provide that structural information.

--
Knut Stolze
DB2 z/OS Utilities Development
IBM Germany

[Serge Rielau]

PJ******@txfb-ins.com wrote:

What would be the purpose/advantage of returning something as a table
(I assume a temp table?) as opposed to a resultset? Have not seen
such before.

SQL Table functions are sometimes called "parameterized views"
That is they have all the benefits and properties of views, but also
allow correlation.
When combining result sets you are stuck literally with nested loop joins.
SQL Table functions extend SQL and hence allow the optimizer to do a
whole lot of interesting stuff.

Cheers
Serge

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

[PJackson]

Many thanks to you all for the good into - fascinating! Would such a
thing work for an SQL PL stored procedure or even a COBOL stored
procedure? We have 8.2 on UDB (where SQL PL would run) and 8.1 I
think on Z/OS where any cobal stuff would run. So what is the scope
of this "table" - treated like a global temp table and confined to
that "session"? The newest IBM book on SQL Pl arrived recently and I
don't recall anything returning results as anything other than result
sets by opening a cursor...
many thanks,

Phil
On May 4, 7:02 am, Serge Rielau <[e-mail]:

[e-mail] wrote:

What would be the purpose/advantage of returning something as a table
(I assume a temp table?) as opposed to a resultset? Have not seen
such before.

SQL Table functions are sometimes called "parameterized views"
That is they have all the benefits and properties of views, but also
allow correlation.
When combining result sets you are stuck literally with nested loop joins.
SQL Table functions extend SQL and hence allow the optimizer to do a
whole lot of interesting stuff.

Cheers
Serge

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

[Serge Rielau]

PJ******@txfb-ins.com wrote:

Many thanks to you all for the good into - fascinating! Would such a
thing work for an SQL PL stored procedure or even a COBOL stored
procedure? We have 8.2 on UDB (where SQL PL would run) and 8.1 I
think on Z/OS where any cobal stuff would run. So what is the scope
of this "table" - treated like a global temp table and confined to
that "session"? The newest IBM book on SQL Pl arrived recently and I
don't recall anything returning results as anything other than result
sets by opening a cursor...
many thanks,

It's like a view. The optimizer may chose to represnt as a temp or
simply pipe the reuslts back.
Keep in mind that this is a FUNCTION that is it is part of an SQL statement.
This is different from a PROCEDURE which is invoked by a CALL statement
What the optimizer does is independent on where this SQL statement is
executed from (SQL PL, Cobol, Java...)

I know that DB2 V8 zOS supports scalar SQL UDF. Not sure about table UDF.
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab