UDF error

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

shsandeep - 30 Jun 2006 03:08 GMT

CREATE FUNCTION MYSCHEMA.FUNCTION1(SEQNAME VARCHAR(100))
RETURNS CHAR(100)
LANGUAGE SQL
MODIFIES SQL DATA

------------------------------------------------------------------------
-- SQL UDF (Scalar)
------------------------------------------------------------------------
F1: BEGIN ATOMIC

DECLARE varsql CHAR(100);

SELECT CONCAT('SEQ=',CHAR(NEXTVAL FOR MYSCHEMA.LOAD_ID)) INTO varsql
FROM SYSIBM.SYSDUMMY1;

INSERT INTO MYSCHEMA.VSQLTEMP VALUES (varsql);

RETURN varsql;
END

The above function gives me the following error. I am new to writing UDFs.
This query runs fine in a SP but not in a UDF. Kindly help me out with the
solution:

[IBM][CLI Driver][DB2/AIX64] SQL0104N An unexpected token "varsql" was
found following "HEMA.LOAD_ID))) INTO". Expected tokens may include:
"<space>". LINE NUMBER=16. SQLSTATE=42601

Reply to this Message

4.spam@mail.ru - 30 Jun 2006 07:15 GMT

Hello.

You aren't allowed to use SELECT INTO statement in UDF, and you aren't
allowed to create a scalar SQL UDF that MODIFIES SQL DATA.
Declare your UDF as RETURNS TABLE (table UDF, not scalar) and you can:
1. use
---
...
SET varsql =
(
SELECT CONCAT('SEQ=',CHAR(NEXTVAL FOR MYSCHEMA.LOAD_ID))
FROM SYSIBM.SYSDUMMY1;
);
...
RETURN values (varsql);
END@
---
2. or rewrite your body as single return clause(without 'begin atomic'
and 'end' clauses) like
---
return
select v
from new table
(
insert into vsqltemp (v)
values(CONCAT('SEQ=',CHAR(NEXTVAL FOR MYSCHEMA.LOAD_ID))
) t
---

Sincerely,
Mark B.

> CREATE FUNCTION MYSCHEMA.FUNCTION1(SEQNAME VARCHAR(100))
> RETURNS CHAR(100)
[quoted text clipped - 23 lines]
> found following "HEMA.LOAD_ID))) INTO". Expected tokens may include:
> "<space>". LINE NUMBER=16. SQLSTATE=42601

Reply to this Message

Serge Rielau - 30 Jun 2006 19:37 GMT

> Hello.
>
[quoted text clipped - 55 lines]
>> found following "HEMA.LOAD_ID))) INTO". Expected tokens may include:
>> "<space>". LINE NUMBER=16. SQLSTATE=42601

SET varsql = CONCAT('SEQ=',CHAR(NEXTVAL FOR MYSCHEMA.LOAD_ID));
will do..
or even better
INSERT INTO MYSCHEMA.VSQLTEMP VALUES (CONCAT('SEQ=',CHAR(NEXTVAL FOR
MYSCHEMA.LOAD_ID)))
or eeeven better (no BEGIN ATOMIC):

CREATE FUNCTION ...
RETURNS TABLE(...)
MODIFIES SQLDATA
RETURN (SELECT varsqlcolinvsqltemp FROM NEW TABLE(INSERT INTO
MYSCHEMA.VSQLTEMP VALUES (CONCAT('SEQ=',CHAR(NEXTVAL FOR MYSCHEMA.LOAD_ID)))

And THAT should fly.

Cheers
Serge

PS:
http://www-128.ibm.com/developerworks/db2/library/techarticle/dm-0411rielau/

Signature

Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

IOD Conference
http://www.ibm.com/software/data/ondemandbusiness/conf2006/

Reply to this Message