LIKE WITH HOST VARIABLE IN SQL UDF

[4.spam]

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.

[Rhino]

<4.****@mail.ru> wrote in message
news:11**********************@i40g2000cwc.googlegr oups.com...

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.

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

[Serge Rielau]

Rhino wrote:

<4.****@mail.ru> wrote in message
news:11**********************@i40g2000cwc.googlegr oups.com...

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.

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
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

[4.spam]

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).

[Serge Rielau]

4.****@mail.ru wrote:

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
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab