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.

<4.spam@mail.ru> wrote in message
news:1148032926.171923.113140@i40g2000cwc.googlegr oups.com...[color=blue]
> 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.
>[/color]
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

Rhino wrote:[color=blue]
> <4.spam@mail.ru> wrote in message
> news:1148032926.171923.113140@i40g2000cwc.googlegr oups.com...[color=green]
>> 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.[/color][/color]
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

Is it described somewhere in documentation?
I'd like to read about other SQL UDF limitations.
[color=blue]
> It a known limitation.
> Local variables and parameters cannot be used in LIKE patterns in
> "inline" SQL PL (That is SQL functions and triggers).[/color]

4.spam@mail.ru wrote:[color=blue]
> Is it described somewhere in documentation?
> I'd like to read about other SQL UDF limitations.[/color]
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