What's incorrect with this function?

[thborges@gmail.com]

I tried create this function with DB2 Express and received the error
message:

-104
Thiago.FUNCTION 1: 9: [IBM][CLI Driver][DB2/NT] SQL0104N An inexpected
token "TABLE SESSION.TESTE(C 1 INTEGER) ON CO" was found following
"ARE GLOBAL TEMPORARY". Expected tokens may include: "<space>". LINE
NUMBER=9. SQLSTATE=42601


CREATE FUNCTION THIAGO.FUNCTION 1( )
RETURNS TABLE (C1 INTEGER)
------------------------------------------------------------------------
-- SQL UDF (Table)
------------------------------------------------------------------------
MODIFIES SQL DATA
BEGIN ATOMIC
DECLARE GLOBAL TEMPORARY TABLE SESSION.TESTE(C 1 INTEGER)
ON COMMIT DELETE ROWS WITH REPLACE NOT LOGGED;

insert into SESSION.TESTE select count(*) from SYSCAT.FUNCTION S
RETURN SELECT C1 from SESSION.TESTE
END

Is some incorrect?

Thanks

[Eugene F]

Re: What's incorrect with this function?

Get rid of SESSION qualifier in the temp table declaration, it's only
required when referencing a temp table.

-Eugene

[Serge Rielau]

Re: What's incorrect with this function?

thborges@gmail. com wrote:[color=blue]
> I tried create this function with DB2 Express and received the error
> message:
>
> -104
> Thiago.FUNCTION 1: 9: [IBM][CLI Driver][DB2/NT] SQL0104N An inexpected
> token "TABLE SESSION.TESTE(C 1 INTEGER) ON CO" was found following
> "ARE GLOBAL TEMPORARY". Expected tokens may include: "<space>". LINE
> NUMBER=9. SQLSTATE=42601
>
>
> CREATE FUNCTION THIAGO.FUNCTION 1( )
> RETURNS TABLE (C1 INTEGER)
> ------------------------------------------------------------------------
> -- SQL UDF (Table)
> ------------------------------------------------------------------------
> MODIFIES SQL DATA
> BEGIN ATOMIC
> DECLARE GLOBAL TEMPORARY TABLE SESSION.TESTE(C 1 INTEGER)
> ON COMMIT DELETE ROWS WITH REPLACE NOT LOGGED;
>
> insert into SESSION.TESTE select count(*) from SYSCAT.FUNCTION S
> RETURN SELECT C1 from SESSION.TESTE
> END
>
> Is some incorrect?[/color]
SQL Functions use "inline SQL PL" which is the subset of SQL PL
supported by "compound statement(dynam ic)". DDL of any shape is NOT
supported. "inline" means that the function is expanded into the query
like a view. Imagine trying to do DDL in a view :-)

In general to extend the capabilities of SQL functions use the CALL
statement in the function and place all the heavy lifting into the
called procedure.

Note though that you can't catch a resultset from the call.

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

[thborges@gmail.com]

Re: What's incorrect with this function?

I'm migrating a Application from the Firebird Database to DB2 and I
need of some resource for migrating complex Stored Procedures that
transform values from tables in database and create a RecordSet return.

With SQL Functions in Java/C this is possible?

[Serge Rielau]

Re: What's incorrect with this function?

thborges@gmail. com wrote:[color=blue]
> I'm migrating a Application from the Firebird Database to DB2 and I
> need of some resource for migrating complex Stored Procedures that
> transform values from tables in database and create a RecordSet return.
>
> With SQL Functions in Java/C this is possible?
>[/color]
DB2 supports SQL procedures as well as C-Procedures.
If you had procedures before that may be the easiest.
Table functions (C/JAVA and to some extend SQL) are also available.

Cheers
Serge

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

[Knut Stolze]

Re: What's incorrect with this function?

thborges@gmail. com wrote:
[color=blue]
> CREATE FUNCTION THIAGO.FUNCTION 1( )
> RETURNS TABLE (C1 INTEGER)
> ------------------------------------------------------------------------
> -- SQL UDF (Table)
> ------------------------------------------------------------------------
> MODIFIES SQL DATA
> BEGIN ATOMIC
> DECLARE GLOBAL TEMPORARY TABLE SESSION.TESTE(C 1 INTEGER)
> ON COMMIT DELETE ROWS WITH REPLACE NOT LOGGED;
>
> insert into SESSION.TESTE select count(*) from SYSCAT.FUNCTION S
> RETURN SELECT C1 from SESSION.TESTE
> END[/color]

Serge told you the "why" this is not working. Here is a simple way to
convert the above into something working:

CREATE FUNCTION THIAGO.FUNCTION 1( )
RETURNS TABLE (C1 INTEGER)
------------------------------------------------------------------------
-- SQL UDF (Table)
------------------------------------------------------------------------
RETURN select count(*) from SYSCAT.FUNCTION S


Depending on your actual logic, I'm pretty confident that you will be able
to solve most of these things with non-procedural SQL.

--
Knut Stolze
DB2 Information Integration Development
IBM Germany

[thborges@gmail.com]

Re: What's incorrect with this function?

Stolze,

The real procedure I need build is more complex. Envolve many
computations and verifications. I sent an example of my necessity.

I see this sample in the ibm public help:

CREATE FUNCTION RANK(N INTEGER)
RETURNS TABLE(
POSITION INTEGER,
EMPNO CHAR(6),
FIRSTNME CHAR(20),
LASTNAME CHAR(20),
SALARY DECIMAL(13,2)
)
LANGUAGE SQL
DISALLOW PARALLEL
MODIFIES SQL DATA
NOT FENCED
BEGIN
DECLARE LAST_SALARY DEC(13,2) DEFAULT 0;
DECLARE I INTEGER DEFAULT 1;
DECLARE STMT VARCHAR(255);
DECLARE TABLE_ALREADY_E XISTS CONDITION FOR '42710'; 1
DECLARE CONTINUE HANDLER FOR TABLE_ALREADY_E XISTS 2
DELETE FROM SESSION.RETURN_ TBL;
DECLARE GLOBAL TEMPORARY TABLE SESSION.RETURN_ TBL ( 3
POSITION INTEGER NOT NULL,
EMPNO CHAR(6) NOT NULL,
FIRSTNME CHAR(20) NOT NULL,
LASTNAME CHAR(20) NOT NULL,
SALARY DECIMAL(13,2) NOT NULL);

FOR_LOOP: FOR EACH_ROW AS C1 CURSOR FOR
SELECT EMPNO, FIRSTNME, LASTNAME, SALARY
FROM SAMPLEDB01.EMPL OYEE ORDER BY SALARY DESC DO

IF (I > N) AND (EACH_ROW.SALAR Y < LAST_SALARY) THEN
LEAVE FOR_LOOP;
ELSE
SET LAST_SALARY = EACH_ROW.SALARY ;
END IF;

INSERT INTO SESSION.RETURN_ TBL
VALUES ( I, EACH_ROW.EMPNO, EACH_ROW.FIRSTN ME,
EACH_ROW.LASTNA ME, EACH_ROW.SALARY );
SET I = I + 1;
END FOR;

RETURN
SELECT POSITION, EMPNO, FIRSTNME, LASTNAME, SALARY
FROM SESSION.RETURN_ TBL;
END;

But I can't create it in DB2 Express edition.
In other versions of DB2 this work proper?
I need some like it.

Thanks

[Serge Rielau]

Re: What's incorrect with this function?

thborges@gmail. com wrote:[color=blue]
> Stolze,
>
> The real procedure I need build is more complex. Envolve many
> computations and verifications. I sent an example of my necessity.
>
> I see this sample in the ibm public help:
>
> CREATE FUNCTION RANK(N INTEGER)
> RETURNS TABLE(
> POSITION INTEGER,
> EMPNO CHAR(6),
> FIRSTNME CHAR(20),
> LASTNAME CHAR(20),
> SALARY DECIMAL(13,2)
> )
> LANGUAGE SQL
> DISALLOW PARALLEL
> MODIFIES SQL DATA
> NOT FENCED
> BEGIN
> DECLARE LAST_SALARY DEC(13,2) DEFAULT 0;
> DECLARE I INTEGER DEFAULT 1;
> DECLARE STMT VARCHAR(255);
> DECLARE TABLE_ALREADY_E XISTS CONDITION FOR '42710'; 1
> DECLARE CONTINUE HANDLER FOR TABLE_ALREADY_E XISTS 2
> DELETE FROM SESSION.RETURN_ TBL;
> DECLARE GLOBAL TEMPORARY TABLE SESSION.RETURN_ TBL ( 3
> POSITION INTEGER NOT NULL,
> EMPNO CHAR(6) NOT NULL,
> FIRSTNME CHAR(20) NOT NULL,
> LASTNAME CHAR(20) NOT NULL,
> SALARY DECIMAL(13,2) NOT NULL);
>
> FOR_LOOP: FOR EACH_ROW AS C1 CURSOR FOR
> SELECT EMPNO, FIRSTNME, LASTNAME, SALARY
> FROM SAMPLEDB01.EMPL OYEE ORDER BY SALARY DESC DO
>
> IF (I > N) AND (EACH_ROW.SALAR Y < LAST_SALARY) THEN
> LEAVE FOR_LOOP;
> ELSE
> SET LAST_SALARY = EACH_ROW.SALARY ;
> END IF;
>
> INSERT INTO SESSION.RETURN_ TBL
> VALUES ( I, EACH_ROW.EMPNO, EACH_ROW.FIRSTN ME,
> EACH_ROW.LASTNA ME, EACH_ROW.SALARY );
> SET I = I + 1;
> END FOR;
>
> RETURN
> SELECT POSITION, EMPNO, FIRSTNME, LASTNAME, SALARY
> FROM SESSION.RETURN_ TBL;
> END;
>
> But I can't create it in DB2 Express edition.
> In other versions of DB2 this work proper?
> I need some like it.[/color]
This for sure won't work on any DB2 for LUW.
And AFAIK DB2 for LUW is the only platform that allows MODIFIES SQL DATA
in table functions to begin with.
Would you mind posting a link to the page where you find tis example?

Cheers
Serge

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

[thborges@gmail.com]

Re: What's incorrect with this function?

I found it in this redbook:

http://www.redbooks.ibm.com/abstracts/sg246503.html

PDF Page: 442

Thanks

[Serge Rielau]

Re: What's incorrect with this function?

thborges@gmail. com wrote:[color=blue]
> I found it in this redbook:
> http://www.redbooks.ibm.com/abstracts/sg246503.html
> PDF Page: 442
>
> Thanks
>[/color]
Interesting. Appears DB2 for iSeries is ahead here.

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

[Gert van der Kooij]

Re: What's incorrect with this function?

In article <1142508713.758 694.216090@i39g 2000cwa.googleg roups.com>,
thborges@gmail. com says...[color=blue]
> I found it in this redbook:
> http://www.redbooks.ibm.com/abstracts/sg246503.html
> PDF Page: 442
>
> Thanks
>
>[/color]

That's the problem.
This redbook is titled:
Stored Procedures, Triggers and User Defined Functions on DB2 Universal
Database for >>>> iSeries <<<<

Examples from this book might work on DB2 for Linux, Windows or Unix but
they are not written for it.

[Knut Stolze]

Re: What's incorrect with this function?

thborges@gmail. com wrote:
[color=blue]
> Stolze,
>
> The real procedure I need build is more complex. Envolve many
> computations and verifications. I sent an example of my necessity.
>
> I see this sample in the ibm public help:
>
> CREATE FUNCTION RANK(N INTEGER)
> RETURNS TABLE(
> POSITION INTEGER,
> EMPNO CHAR(6),
> FIRSTNME CHAR(20),
> LASTNAME CHAR(20),
> SALARY DECIMAL(13,2)
> )
> LANGUAGE SQL
> DISALLOW PARALLEL
> MODIFIES SQL DATA
> NOT FENCED
> BEGIN
> DECLARE LAST_SALARY DEC(13,2) DEFAULT 0;
> DECLARE I INTEGER DEFAULT 1;
> DECLARE STMT VARCHAR(255);
> DECLARE TABLE_ALREADY_E XISTS CONDITION FOR '42710'; 1
> DECLARE CONTINUE HANDLER FOR TABLE_ALREADY_E XISTS 2
> DELETE FROM SESSION.RETURN_ TBL;
> DECLARE GLOBAL TEMPORARY TABLE SESSION.RETURN_ TBL ( 3
> POSITION INTEGER NOT NULL,
> EMPNO CHAR(6) NOT NULL,
> FIRSTNME CHAR(20) NOT NULL,
> LASTNAME CHAR(20) NOT NULL,
> SALARY DECIMAL(13,2) NOT NULL);
>
> FOR_LOOP: FOR EACH_ROW AS C1 CURSOR FOR
> SELECT EMPNO, FIRSTNME, LASTNAME, SALARY
> FROM SAMPLEDB01.EMPL OYEE ORDER BY SALARY DESC DO
>
> IF (I > N) AND (EACH_ROW.SALAR Y < LAST_SALARY) THEN
> LEAVE FOR_LOOP;
> ELSE
> SET LAST_SALARY = EACH_ROW.SALARY ;
> END IF;
>
> INSERT INTO SESSION.RETURN_ TBL
> VALUES ( I, EACH_ROW.EMPNO, EACH_ROW.FIRSTN ME,
> EACH_ROW.LASTNA ME, EACH_ROW.SALARY );
> SET I = I + 1;
> END FOR;
>
> RETURN
> SELECT POSITION, EMPNO, FIRSTNME, LASTNAME, SALARY
> FROM SESSION.RETURN_ TBL;
> END;[/color]

If I get this right, then you select some rows from the EMPLOYEE table,
insert into into a temp table and then return the stuff in the temp table.
At the same time, the parameter N limits the number of rows processed. Is
that right? If so, the following should do something along those lines:

CREATE FUNCTION rank(n INTEGER)
RETURNS TABLE (
position INTEGER,
empno CHAR(6),
firstnme CHAR(20),
lastname CHAR(20),
salary DECIMAL(13, 2) )
LANGUAGE SQL
RETURN
SELECT *
FROM ( SELECT row_number() OVER ( ORDER BY salary DESC
AS rn,
empno, firstnmo, lastname, salary
FROM sampledb01.empl oyee ) AS t
WHERE rn < n

Ok, this is a bit different than your statement because it does not return
all the rows beyond the first N where salary is equal to the salary of the
N-th row. But this could be added if needed.

--
Knut Stolze
DB2 Information Integration Development
IBM Germany