CTE in function

[dean.cochrane]

I'm trying to do something a little odd, mostly as an experiment. I'm
trying to create a function which will return 'True' if the string
passed in is a valid integer, and 'False' if it is not. I'm doing this
in this way because there is an existing function in the database that
is quite slow, and I believe that this method will be faster, if more
difficult to maintain. And I'm a geek, and it's cool to be able to
parse a string in SQL.

Anyway, the problem I have is that DB2 throws SQL0104N, claiming that
the vOUT after INTO is an unexpected token. I have had trouble getting
procedures with CTEs in them to compile consistently, and I'm not sure
why this is failing. Is SELECT...INTO not allowed inside a function?
CREATE FUNCTION ISINTEGER_2(
iSTR VARCHAR(255)
)
RETURNS VARCHAR(5)
DETERMINISTIC
NO EXTERNAL ACTION
LANGUAGE SQL
BEGIN ATOMIC
DECLARE vOUT VARCHAR(5);

SET vOUT = 'True';

WITH ints(n) AS (
SELECT 0
FROM DUAL
UNION ALL
SELECT 1 + n FROM ints WHERE n < 10),
A(A) AS (VALUES(iSTR))
SELECT 'False'
INTO vOUT
FROM A, ints i

WHERE i.n <= length(A.A)
AND i.n 0
AND char(substr(ltrim(rtrim(A.A)), i.n, 1)) NOT IN (SELECT
char(n) FROM ints);
RETURN vOUT;

END
;

[4.spam]

On Oct 22, 9:54 pm, "[e-mail]"
<[e-mail]:

I'm trying to do something a little odd, mostly as an experiment. I'm
trying to create a function which will return 'True' if the string
passed in is a valid integer, and 'False' if it is not. I'm doing this
in this way because there is an existing function in the database that
is quite slow, and I believe that this method will be faster, if more
difficult to maintain. And I'm a geek, and it's cool to be able to
parse a string in SQL.

Anyway, the problem I have is that DB2 throws SQL0104N, claiming that
the vOUT after INTO is an unexpected token. I have had trouble getting
procedures with CTEs in them to compile consistently, and I'm not sure
why this is failing. Is SELECT...INTO not allowed inside a function?

CREATE FUNCTION ISINTEGER_2(
iSTR VARCHAR(255)
)
RETURNS VARCHAR(5)
DETERMINISTIC
NO EXTERNAL ACTION
LANGUAGE SQL
BEGIN ATOMIC
DECLARE vOUT VARCHAR(5);

SET vOUT = 'True';

WITH ints(n) AS (
SELECT 0
FROM DUAL
UNION ALL
SELECT 1 + n FROM ints WHERE n < 10),
A(A) AS (VALUES(iSTR))
SELECT 'False'
INTO vOUT
FROM A, ints i

WHERE i.n <= length(A.A)
AND i.n 0
AND char(substr(ltrim(rtrim(A.A)), i.n, 1)) NOT IN (SELECT
char(n) FROM ints);

RETURN vOUT;

END
;

You can't use SELECT INTO in SQL UDF.
Use
SET vOUT=(select ...)
instead.
May be this function will help you:
---
create function is_int(v varchar(16))
returns int
language sql
deterministic
contains sql
return
(
select
case
when translate(v, X'FF', '0123456789', X'FF')=repeat(X'FF',
nullif(length(v), 0))
then 1
else 0
end
from table(values substr(trim(v), case when substr(trim(v), 1, 1) in
('+', '-') then 2 else 1 end)) b(v)
)
---

Sincerely,
Mark B.