SEO

The initial row is inserted with the colPartNum column containing a valid
LIKE pattern, such as (without the single quotes) 'AB%DE'.
I want to update the column value with the results of a query against a
different table (that uses the LIKE predicate) but cannot get around the
SQL0132 error .
I have tried the hex notation after the LIKE such as (without the
quotes)...
" where colNewPartNum like ( X'27' || nnn.colPartNum || X'27) " , but
still get this same error....

See
http://groups.google.com/groups?q=sq... oo.com&rnum=4
and
http://forums.devshed.com/archive/t-169709 for similar issues ... but no
answers that I can find on google or the newsgroups...

Anyone know the syntax trick to getting this to work?
Thanks...

--#SET DELIMITER !
drop trigger Test1!

CREATE TRIGGER Test1
AFTER INSERT ON USER1.ORDERS
REFERENCING NEW AS NNN
FOR EACH ROW MODE DB2SQL

BEGIN ATOMIC
UPDATE ORDERS
SET colPartNum = (select colNewPartNum from tblMasterParts
where colNewPartNum like (nnn.colPartNum))
where colPartNum = nnn.colPartNum ;
END!
COMMIT!

That's a bit of a trap. LIKE only allows for constant patterns.
Thsi excludes local or trigger transition variables in side of inline
SQL PL (as used in triggers).
Teh migration tool kit (MTK) provides general LIKE functions which you
can download for free if that's a major issue for you.

Cheers
Serge

"Bill Smith" <x@x.com> wrote in message news:<Xep8d.4405$kC2.3836@fe2.columbus.rr.com>...
> The initial row is inserted with the colPartNum column containing a valid
> LIKE pattern, such as (without the single quotes) 'AB%DE'.
> I want to update the column value with the results of a query against a
> different table (that uses the LIKE predicate) but cannot get around the
> SQL0132 error .
DB2 UDB SQL Reference Volume 1 -> Chapter 2. Language elements ->
Predicates -> LIKE predicate:
pattern-expression
An expression that specifies the string that is to be matched.
The expression can be specified by:
v A constant
v A special register
v A host variable
v A scalar function whose operands are any of the above
v An expression concatenating any of the above

So, you can't use a column for pattern-expression.

Yes, This functionality is a major issue for me. I need to find some way to
update the column value from the pattern to a result that matches the
pattern. Are you saying I can get the functionality I want by calling an
external function that resides in the MTK?
Bill
"Serge Rielau" <srielau@ca.ibm.com> wrote in message
news:2sfd6dF1k3sboU2@uni-berlin.de...
> That's a bit of a trap. LIKE only allows for constant patterns.
> Thsi excludes local or trigger transition variables in side of inline SQL
> PL (as used in triggers).
> Teh migration tool kit (MTK) provides general LIKE functions which you can
> download for free if that's a major issue for you.
>
> Cheers
> Serge

Bill Smith wrote:

> Yes, This functionality is a major issue for me. I need to find some way to
> update the column value from the pattern to a result that matches the
> pattern. Are you saying I can get the functionality I want by calling an
> external function that resides in the MTK?
> Bill
Correct. Just download the MTK and go shopping.

There are a bunch of interesting Sybase/SQL Server and Oracle functions
you can snicker.

Cheers
Serge

Serge Rielau wrote:

> Bill Smith wrote:
>
>> Yes, This functionality is a major issue for me. I need to find some way
>> to update the column value from the pattern to a result that matches the
>> pattern. Are you saying I can get the functionality I want by calling an
>> external function that resides in the MTK?
>> Bill
> Correct. Just download the MTK and go shopping.
>
> There are a bunch of interesting Sybase/SQL Server and Oracle functions
> you can snicker.

I don't know if the MTK provides a regular expression matching function. If
it doesn't and if you need regexp matching, then you can build your own
function:
http://www-106.ibm.com/developerwork...301stolze.html

--
Knut Stolze
Information Integration
IBM Germany / University of Jena

Thanks but I didn't really want to have to code an external function to get
the basic LIKE functionality. I don't need the regular expression (but I
bookmarked the URL for a future project I have in mind).
Do you know if a SQL language UDF can perform the functionality? Ie Can I
create an SQL language UDF that accepts the pattern as a varchar, the UDF
uses the varchar on the right side of the LIKE predicate for a table lookup,
and return the single column, single row result of the lookup to the trigger
for use in the update statement?

I didn't think it would be so hard to just have the pattern stored in the
database as opposed to being hardcoded in the statement.
Bill

"Knut Stolze" <stolze@de.ibm.com> wrote in message
news:ck02pp$79o$1@fsuj29.rz.uni-jena.de...
> Serge Rielau wrote:
>
>> Bill Smith wrote:
>>
>>> Yes, This functionality is a major issue for me. I need to find some way
>>> to update the column value from the pattern to a result that matches the
>>> pattern. Are you saying I can get the functionality I want by calling an
>>> external function that resides in the MTK?
>>> Bill
>> Correct. Just download the MTK and go shopping.
>>
>> There are a bunch of interesting Sybase/SQL Server and Oracle functions
>> you can snicker.
>
> I don't know if the MTK provides a regular expression matching function.
> If
> it doesn't and if you need regexp matching, then you can build your own
> function:
> http://www-106.ibm.com/developerwork...301stolze.html
>
> --
> Knut Stolze
> Information Integration
> IBM Germany / University of Jena

Do you know of any solution that does not require the use of an external
function? (an SQL language UDF?, combo of SQL UDF and stored proc?,etc.)

To summarize the issue again ...
When a row is inserted into a table containing a column value 'Jo_es' , I
want to change it to 'Jones' based on a simple query against a tblNames
table using sql similar to
select name from tblNames where name like 'Jo_es'
It's just that 'Jo_es' is not hard coded but comes from the result of a
query ..


Thanks,
Bill
"Serge Rielau" <srielau@ca.ibm.com> wrote in message
news:2sgsu2F1jkm8pU1@uni-berlin.de...
> Bill Smith wrote:
>
>> Yes, This functionality is a major issue for me. I need to find some way
>> to update the column value from the pattern to a result that matches the
>> pattern. Are you saying I can get the functionality I want by calling an
>> external function that resides in the MTK?
>> Bill
> Correct. Just download the MTK and go shopping.
>
> There are a bunch of interesting Sybase/SQL Server and Oracle functions
> you can snicker.
>
> Cheers
> Serge

Bill Smith wrote:

> Do you know of any solution that does not require the use of an external
> function? (an SQL language UDF?, combo of SQL UDF and stored proc?,etc.)

You could of course use the built-in functions like LOCATE and other string
functions to do the pattern matching. And the result of that can be
wrapped into a UDF.

--
Knut Stolze
Information Integration
IBM Germany / University of Jena

The trick to doing this is to use a suitable UDB facility. Forget about
the trigger because, as Serge initially commented, row variables AND
LOCALLY DEFINED STRING VARIABLES are not usable in a LIKE clause.

UDB does, however, have a mechanism that can do this.

Instead of inserting the row from your application then updating it in a
trigger, use a stored procedure to do everything. Pass all of the column
values to the stored procedure for the insert. You can use the passed
string to locate the "new" data value (using LIKE - UDB 8.1 FP 7),
insert your logging record and, as a performance bonus, do a single
insert of the data row avoiding two logging actions.

If your column data values do not include the wildcard characters used
by LIKE, a "before" trigger can be used to raise an error if the
wildcard characters appear in the column data. This will prevent an
insert without using the stored procedure.

Phil Sherman


Bill Smith wrote:
> Do you know of any solution that does not require the use of an external
> function? (an SQL language UDF?, combo of SQL UDF and stored proc?,etc.)
>
> To summarize the issue again ...
> When a row is inserted into a table containing a column value 'Jo_es' , I
> want to change it to 'Jones' based on a simple query against a tblNames
> table using sql similar to
> select name from tblNames where name like 'Jo_es'
> It's just that 'Jo_es' is not hard coded but comes from the result of a
> query ..
>
>
> Thanks,
> Bill
> "Serge Rielau" <srielau@ca.ibm.com> wrote in message
> news:2sgsu2F1jkm8pU1@uni-berlin.de...
>
>>Bill Smith wrote:
>>
>>
>>>Yes, This functionality is a major issue for me. I need to find some way
>>>to update the column value from the pattern to a result that matches the
>>>pattern. Are you saying I can get the functionality I want by calling an
>>>external function that resides in the MTK?
>>>Bill
>>
>>Correct. Just download the MTK and go shopping.
>>
>>There are a bunch of interesting Sybase/SQL Server and Oracle functions
>>you can snicker.
>>
>>Cheers
>>Serge
>
>
>