问题描述
插入初始行时,colPartNum列包含有效的
LIKE模式,例如(不带单引号)''AB%DE''。
我想用针对
不同表(使用LIKE谓词)的查询结果更新列值,但无法绕过
SQL0132错误。
我在LIKE之后尝试了十六进制表示法(没有
引号)...
" ;其中colNewPartNum喜欢(X''27''|| nnn.colPartNum || X''27)" ,但是
仍然会得到同样的错误....
见
和
针对类似问题......但是没有
答案,我可以在谷歌或新闻组中找到...
任何人都知道让它工作的语法技巧吗?
谢谢......
- #SET DELIMITER!
掉落触发器Test1!
创建触发器测试1
插入USER1.ORDERS后
重新推荐新的内容
适用于每种行模式DB2SQL
BEGIN ATOMIC
更新订单
SET colPartNum =(从tblMaster中选择colNewPartNum零件
其中colNewPartNum喜欢(nnn.colPartNum))
其中colPartNum = nnn.colPartNum;
END!
COMMIT!
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!
推荐答案
DB2 UDB SQL参考卷1 - >第2章语言元素 - >
谓词 - > LIKE谓词:
pattern-expression
一个表达式,指定要匹配的字符串。
表达式可以通过以下方式指定:
v一个常数
v一个特殊的寄存器
v一个主变量
v一个标量函数,其操作数是上述任何一个
v连接上述任何一个的表达式
所以,你不能使用一个列进行模式表达。
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.
这篇关于在更新触发器中帮助使用类似谓词的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!