SQLCode = -138在大计数表上但不是在小DB2上进行标记 [英] SQLCode=-138 flagging on large count tables but not small DB2

问题描述

我构建了一个视图，在其中使用以下代码从格式为姓氏，名字"的字段中仅提取姓氏:

I built a view in which I'm using the following code to pull only the Last name from a field that is formatted 'LastName,FirstName':

VALUE(RTRIM(SUBSTR(A.PREVIOUS_NAMES,1,LOCATE(',', A.PREVIOUS_NAMES)-1)), '') 
AS "PREVIOUS_NAME",                                                         

当我在沙盒环境中工作时，此视图工作正常，但是当我将其向上测试以尝试在DBVisualizer中打开视图时，数据结果提供了错误:

This view worked fine as I was working in our sandbox environment, but as I moved it up to test, and try to open the view in DBVisualizer, the data results provide the Error:

THE SECOND OR THIRD ARGUMENT OF THE SUBSTR OR SUBSTRING FUNCTION IS OUT OF 
RANGE. SQLCODE=-138, SQLSTATE=22011, DRIVER=4.22.29

沙盒的记录接近11000条，其中测试和生产系统接近120万条记录，因此我猜这是由于该段代码的复杂性而导致的性能问题.该视图似乎仍已创建，我可以对其进行查询并返回特定的结果/结果集，我想我担心它是否可用于大型查询，或者在这种情况下是否确实需要关注性能?

The sandbox has just under 11k records, where the test and prod systems are closer to 1.2 million, so I'm guessing this is a performance issue as a result of complexity of that piece of the code. The view still appears to have been created, I can query it and return a specific result/set of results, I guess I'm concerned on if it is usable for a large query, or if performance really is a concern in this case?

是否有更好的方法可以解析姓氏而不必在定位函数上使用子字符串?

Is there a better way I can parse that last name without having to use the substring on a locate function?

感谢您的帮助！

推荐答案

很有可能不是性能问题，只是有些行的数据没有逗号

Most likely it is not a performance issue, just some rows have data with no comma

这篇关于SQLCode = -138在大计数表上但不是在小DB2上进行标记的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！