LIKE WITH HOST VARIABLE IN SQL UDF

#1

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.

#2

<4.spam (AT) mail (DOT) ru> wrote

Quote:

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

#3

Rhino wrote:

Quote:

4.spam (AT) mail (DOT) ru> wrote in message
news:1148032926.171923.113140 (AT) i40g2000cwc (DOT) googlegroups.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

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

Quote:

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

#5

4.spam (AT) mail (DOT) ru wrote:

Quote:

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

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

LIKE WITH HOST VARIABLE IN SQL UDF

comp.databases.ibm-db2 comp.databases.ibm-db2

LIKE WITH HOST VARIABLE IN SQL UDF - 05-19-2006 , 05:02 AM

Re: LIKE WITH HOST VARIABLE IN SQL UDF - 05-19-2006 , 07:51 AM

Re: LIKE WITH HOST VARIABLE IN SQL UDF - 05-20-2006 , 10:10 AM

Re: LIKE WITH HOST VARIABLE IN SQL UDF - 05-22-2006 , 01:19 AM

Re: LIKE WITH HOST VARIABLE IN SQL UDF - 05-22-2006 , 10:26 AM