By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
437,610 Members | 1,677 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 437,610 IT Pros & Developers. It's quick & easy.

CTE in function

P: n/a
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
;

Oct 22 '07 #1
Share this Question
Share on Google+
1 Reply


P: n/a
On Oct 22, 9:54 pm, "dean.cochr...@gmail.com"
<dean.cochr...@gmail.comwrote:
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.

Oct 23 '07 #2

This discussion thread is closed

Replies have been disabled for this discussion.