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

SQL UDF works very strange. Bug or feature?

P: n/a
8.2.2 (fixpack 9)

Simple function - corrects call for substr. But doesn't work.
Look this sample - any call with wrong Idx

values( Test_Substr('01234567890123456789', -1 ))

SQL0901N The SQL statement failed because of a non-severe system
error.
Subsequent SQL statements can be processed. (Reason "Invalid position
or length parameters".) SQLSTATE=58004

CREATE FUNCTION Test_Substr
(vStr VARCHAR(20), Idx INTEGER
)
RETURNS CHARACTER(1)
LANGUAGE SQL
DETERMINISTIC
CONTAINS SQL
return
case
when Idx < 1 THEN '0'
when LENGTH(vStr) < Idx THEN '1'
when LENGTH(vStr) >= Idx THEN SUBSTR(vStr, Idx, 1)
else 'x'
end
@

Rewritten UDF for test case work correctly...

drop FUNCTION Test_Substr

DB20000I The SQL command completed successfully.

CREATE FUNCTION Test_Substr
(vStr VARCHAR(20), Idx INTEGER
)
RETURNS CHARACTER(1)
LANGUAGE SQL
DETERMINISTIC
CONTAINS SQL
return
case
when Idx < 1 THEN '0'
when LENGTH(vStr) < Idx THEN '1'
when LENGTH(vStr) >= Idx THEN '!'
else 'x'
end
DB20000I The SQL command completed successfully.

values( Test_Substr('01234567890123456789', -1 ))

1

-

0

1 record(s) selected.

values( Test_Substr('01234567890123456789', 2 ))

1

-

!

1 record(s) selected.

values( Test_Substr('01234567890123456789', 32 ))

1

-

1

1 record(s) selected.

Nov 12 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a
bughunter@ru wrote:
8.2.2 (fixpack 9)

Simple function - corrects call for substr. But doesn't work.
Look this sample - any call with wrong Idx

values( Test_Substr('01234567890123456789', -1 ))

SQL0901N The SQL statement failed because of a non-severe system
error.
Subsequent SQL statements can be processed. (Reason "Invalid position
or length parameters".) SQLSTATE=58004

CREATE FUNCTION Test_Substr
(vStr VARCHAR(20), Idx INTEGER
)
RETURNS CHARACTER(1)
LANGUAGE SQL
DETERMINISTIC
CONTAINS SQL
return
case
when Idx < 1 THEN '0'
when LENGTH(vStr) < Idx THEN '1'
when LENGTH(vStr) >= Idx THEN SUBSTR(vStr, Idx, 1)
else 'x'
end
@

This is an incarnation of IY70983 (will be fixed in FP10).
You should get a -138 though, instead of a -901, I'll look into that.

The problem lies in overzealous "constant folding" and (in case of
variable) pre-execution.
You can try this for a workaround:
THEN SUBSTR(vStr, Idx, COALESCE(1, INT(RAND())))

Cheers
Serge
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab
Nov 12 '05 #2

P: n/a
Strangely enough, this works fine on my RHEL4 Linux (2.6 kernel) DB2 8.1
FP9a! Maybe it was fixed a little early.

Phil Sherman

Serge Rielau wrote:
This is an incarnation of IY70983 (will be fixed in FP10).
You should get a -138 though, instead of a -901, I'll look into that.

The problem lies in overzealous "constant folding" and (in case of
variable) pre-execution.
You can try this for a workaround:
THEN SUBSTR(vStr, Idx, COALESCE(1, INT(RAND())))

Cheers
Serge

Nov 12 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.