本文介绍了在更新触发器中帮助使用类似谓词的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述



插入初始行时,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.




这篇关于在更新触发器中帮助使用类似谓词的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!

05-28 21:30
查看更多