473,554 Members | 2,340 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Help with like predicate in update trigger


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...hoo.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!
Nov 12 '05 #1
18 5949
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
Nov 12 '05 #2
"Bill Smith" <x@x.com> wrote in message news:<Xe******* **********@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.
Nov 12 '05 #3
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" <sr*****@ca.ibm .com> wrote in message
news:2s******** *****@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

Nov 12 '05 #4
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
Nov 12 '05 #5
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
Nov 12 '05 #6
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" <st****@de.ibm. com> wrote in message
news:ck******** **@fsuj29.rz.un i-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

Nov 12 '05 #7
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" <sr*****@ca.ibm .com> wrote in message
news:2s******** *****@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

Nov 12 '05 #8
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
Nov 12 '05 #9
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" <sr*****@ca.ibm .com> wrote in message
news:2s******** *****@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



Nov 12 '05 #10

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
935
by: Thierry Marneffe | last post by:
Hello I would like to get the content of a field based in the field Name. Suppose a table with a field Named 'LastName' for wich there is a trigger after update I store the field name in a local variable Set @ColName = 'LastName' How can I retrieve the value of the @ColName from the inserted table using
3
1758
by: Curtis Gilchrist | last post by:
I'm trying my hand at triggers and it doesn't seem to be working for me. I have a very simple database that consists of one table: Employees. I want to create a trigger that will limit the EMP_TITLE field to either Ms., Mr., or Mrs. I am using the following code: CREATE trigger triTitleCheck ON employee FOR insert, update AS declare...
9
3449
by: Martin | last post by:
Hello, I'm new with triggers and I can not find any good example on how to do the following: I have two tables WO and PM with the following fields: WO.WONUM, VARCHAR(10) WO.PMNUM, VARCHAR(10) WO.PROBLEMCODE, VARCHAR(8)
11
3918
by: Jules Alberts | last post by:
Hello everybody, Someone helped me earlier with this TCL trigger function: create or replace function tlow() returns trigger as ' set NEW($1) return ' language 'pltcl'; I use it to force lowercase of values inserted in the db. There is one
4
1944
by: SUKRU | last post by:
Hello everybody. Unfortunately I am pretty new to sql-server 2000 I need some help with a Trigger I created. I created a trigger witch takes the id of the affected row and does a update on a other table with that ID. The trigger works fine with one affected row. But when there are more then one rows affected, i get an error. I found out...
3
3711
by: teddysnips | last post by:
I need a trigger (well, I don't *need* one, but it would be optimal!) but I can't get it to work because it references ntext fields. Is there any alternative? I could write it in laborious code in the application, but I'd rather not! DDL for table and trigger below. TIA
1
1542
by: rdraider | last post by:
Hi all, I know squat about triggers so was hoping somebody could point me in the right direction. I wanted to copy an email address field from a salesman table to a note field in a customer table. Seems easy enough for a one time update. But I would like to add a trigger to auto-update the customer table anytime an email address changes in...
15
2560
by: Jay | last post by:
I have a multi threaded VB.NET application (4 threads) that I use to send text messages to many, many employees via system.timer at a 5 second interval. Basically, I look in a SQL table (queue) to determine who needs to receive the text message then send the message to the address. Only problem is, the employee may receive up to 4 of the...
11
7850
by: tracy | last post by:
Hi, I really need help. I run this script and error message appeal as below: drop trigger log_errors_trig; drop trigger log_errors_trig ERROR at line 1: ORA04080: trigger 'LOG_ERRORS-TRIG' does not exist drop table log_errors_tab;
0
7612
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main...
0
7536
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language...
0
7814
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. ...
0
8053
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that...
1
7576
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For...
0
7895
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the...
0
6163
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then...
1
5443
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes...
0
5165
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.