CODEWITHSUNDEEP
sqlteradatateradata-sql-assistant
Debasis Das
Debasis Das

Reputation: 57

Teradata SELECT Failed: [2663] SUBSTR: string subscript out of bounds

I have below piece of code which is failing. 

,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

i debugged and found problem was with SUBSTR(PERSON_DIM.PERSON_NAME, 1,INDEX(PERSON_DIM.PERSON_NAME,',' )-1 part.

 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 '%,%';

PERSON_NAME that contains ',' are running fine. Can you Please guide me with how to frame query to avoid this error.

Upvotes: 1

Views: 1183

Answers (3)

dnoeth
dnoeth

Reputation: 60502

The shortest way utilizes STRTOK:

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

No need for nested function or adding a comma or CASE ...

Upvotes: 1

Debasis Das
Debasis Das

Reputation: 57

Thanks for the answers.

I have tried below and it worked as well.

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

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1271023

Presumably, some names don't have a comma. I think the simplest method is to just add one:

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

Upvotes: 2

Related Questions