'Like' function in update

[* Tong *]

Hi,

Why the following code is not working:

UPDATE ta SET ta.val =
(SELECT val FROM tb
WHERE ta.id like (tb.id || '%' ))

SQL0132N A LIKE predicate or POSSTR scalar function is not valid because the
first operand is not a string expression or the second operand is not a
string. SQLSTATE=42824

If I change the tb.id in the "||" expression to a string, the above
code will work.

You guys have a explanation to defense it this time? :-) Any work
around? Thanks.

--
Tong (remove underscore(s) to reply)
*niX Power Tools Project: http://xpt.sourceforge.net/
http://members.xoom.com/suntong001/
- All free contribution & collection

[Serge Rielau]

Hi Tong,

LIKE currently doesn't digest non constant expressions.

A work around might be:

LENGTH(ta.id) >= LENGTH(tb.id) AND tb.id = SUBSTR(ta.id, 1, LENGTH(tb.id))

Cheers
Serge

[Serge Rielau]

Hi Tong,

LIKE currently doesn't digest non constant expressions.

A work around might be:

LENGTH(ta.id) >= LENGTH(tb.id) AND tb.id = SUBSTR(ta.id, 1, LENGTH(tb.id))

Cheers
Serge

[* Tong *]

Serge Rielau <sri...@ca.ibm.com> writes:

> Hi Tong,

>
> A work around might be:

Thanks. Serge