LIKE WITH HOST VARIABLE IN SQL UDF

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

4.spam@mail.ru - 19 May 2006 11:02 GMT

Hello.

UDB DB2 v8.2.1 for LUW.
Why this function can't be compiled?
---
CREATE FUNCTION MYLIKE(NM VARCHAR(128))
LANGUAGE SQL
RETURNS INTEGER
BEGIN ATOMIC
RETURN
(
SELECT COUNT(1)
FROM SYSCAT.TABLES
WHERE TABNAME LIKE MYLIKE.NM
);
END@
---
SP with like with host variable is compiled successfully.
I think it is a bug.

Sincerely,
Mark B.
Mark B.

Reply to this Message

Rhino - 19 May 2006 13:51 GMT

> Hello.
>
[quoted text clipped - 15 lines]
> SP with like with host variable is compiled successfully.
> I think it is a bug.

I think it would be easier to guess the problem if you told us the error
message from the compiler.

The only obvious problem I see is 'select count(1)' instead of 'select
count(*)' but 'count(1)' may be a valid expression, even if it doesn't do
what you want, so I'm not sure if it would cause the compile to fail.

--
Rhino

Reply to this Message

Serge Rielau - 20 May 2006 16:10 GMT

>> Hello.
>>
[quoted text clipped - 15 lines]
>> SP with like with host variable is compiled successfully.
>> I think it is a bug.

It a known limitation.
Local variables and parameters cannot be used in LIKE patterns in
"inline" SQL PL (That is SQL functions and triggers).

Cheers
Serge

Signature

Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

Reply to this Message

4.spam@mail.ru - 22 May 2006 07:19 GMT

Is it described somewhere in documentation?
I'd like to read about other SQL UDF limitations.

> It a known limitation.
> Local variables and parameters cannot be used in LIKE patterns in
> "inline" SQL PL (That is SQL functions and triggers).

Reply to this Message

Serge Rielau - 22 May 2006 16:26 GMT

> Is it described somewhere in documentation?
> I'd like to read about other SQL UDF limitations.

Yes and no. The explanation for LIKE says that the pattern must be a
"constant expression". That expression includes host-variables.
Host-variables are equated with SQL Variables in SQL Procedures (but not
- in this case - functions).
This limitation is really special and teh only on eof its kind.
In general "inline" SQL PL is described in "compound statement (dynamic)".

Cheers
Serge

Signature

Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

Reply to this Message