teradata select失败：[2663]substr:字符串下标超出界限

hc2pp10m 于 2021-07-26 发布在 Java

关注(0)|答案(3)|浏览(366)

我有下面一段失败的代码。

,COALESCE(SAP.LAST_NAME, SUBSTR(PERSON_DIM.PERSON_NAME, 1,INDEX(PERSON_DIM.PERSON_NAME,',' )-1)) AS Lastname

Error: SELECT Failed: [2663] SUBSTR: string subscript out of bounds in PERSON_NAME

我调试并发现substr（person\u dim.person\u name，1，index（person\u dim.person\u name，，'）-1部分有问题。

SEL PERSON_NAME,INDEX(PERSON_NAME,',' )-1 FROM NDW_SHARED_PII_VIEWS.PERSON_DIM WHERE PERSON_NAME NOT LIKE '%,%';--Giving us results
 output:
 Star Installations Inc -1
 Unassigned -1
 Cable Services Company Inc.    -1

 SEL SUBSTR(PERSON_NAME, 1,INDEX(PERSON_NAME,',' )-1) FROM NDW_SHARED_PII_VIEWS.PERSON_DIM WHERE PERSON_NAME NOT LIKE '%,%';--Failing

 Above query eventually becomes like below which is causing confusion i believe. 

 SEL SUBSTR(PERSON_NAME, 1,-1) FROM NDW_SHARED_PII_VIEWS.PERSON_DIM WHERE PERSON_NAME NOT LIKE '%,%';

包含“”的人名运行正常。你能指导我如何框架查询以避免这个错误吗。

sql teradata teradata-sql-assistant

来源：https://stackoverflow.com/questions/62019859/teradata-select-failed-2663-substr-string-subscript-out-of-bounds

3条答案

按热度按时间

fnatzsnv1#

大概，有些名字没有逗号。我认为最简单的方法就是添加一个：

COALESCE(SAP.LAST_NAME,
         SUBSTR(PERSON_DIM.PERSON_NAME, 1, INDEX(PERSON_DIM.PERSON_NAME || ',', ',' ) - 1
               )
        ) AS Lastname

赞(0）回复(0）举报 2021-07-26

e0bqpujr2#

谢谢你的回答。
我在下面试过，效果也不错。

COALESCE(SAP.LAST_NAME,
    CASE WHEN PERSON_DIM.PERSON_NAME NOT LIKE '%,%' 
            THEN PERSON_DIM.PERSON_NAME
    ELSE 
            SUBSTR(PERSON_DIM.PERSON_NAME,1,INDEX(PERSON_DIM.PERSON_NAME,',' )-1) END) AS LASTNAME

赞(0）回复(0）举报 2021-07-26

gj3fmq9x3#

最短的路 STRTOK :

COALESCE(SAP.LAST_NAME, StrTok(PERSON_DIM.PERSON_NAME,',',1))

不需要嵌套函数或添加逗号或大小写。。。

赞(0）回复(0）举报 2021-07-26

我来回答

teradata select失败：[2663]substr:字符串下标超出界限

3条答案

相关问题

热门标签

最新问答