469,578 Members | 1,869 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,578 developers. It's quick & easy.

UDF error

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

Jun 30 '06 #1
2 1834
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)
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


Jun 30 '06 #2
4.****@mail.ru wrote:
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)
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

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/developerwork...dm-0411rielau/

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

IOD Conference
http://www.ibm.com/software/data/ond...ness/conf2006/
Jun 30 '06 #3

This discussion thread is closed

Replies have been disabled for this discussion.

By using this site, you agree to our Privacy Policy and Terms of Use.