1

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.

Debasis Das
  • 21
  • 4
  • What do you want to do with the names that don't contain a comma? – HoneyBadger May 26 '20 at 10:21
  • You can leave it as blank as for first name we are already picking up full company name if PERSON_NAME is not an individual person. – Debasis Das May 26 '20 at 10:26
  • SELECT PERSON_NAME,SUBSTR(PERSON_DIM.PERSON_NAME, INDEX(PERSON_DIM.PERSON_NAME,',' ) +1 ,length(PERSON_DIM.PERSON_NAME)) FROM NDW_SHARED_PII_VIEWS.PERSON_DIM WHERE PERSON_NAME NOT LIKE '%,%'; – Debasis Das May 26 '20 at 10:27
  • Output: Makotek LLC Makotek LLC Non Pay Disconnects Non Pay Disconnects Vitel Communications LLC Vitel Communications LLC Laguna, Michell Michell Kelly, Shawn Michael Shawn Michael Behinan, Arsene Quentin Dieudonne Arsene Quentin Dieudonne Brookins, Laura Laura – Debasis Das May 26 '20 at 10:28
  • Please edit your question to include that sample data and query. Comments are not easy to read – HoneyBadger May 26 '20 at 10:38
  • You can either use a CASE for the different rules or maybe use `StrTok(PERSON_NAME,',',1)` – dnoeth May 26 '20 at 10:41

3 Answers3

1

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
Gordon Linoff
  • 1,122,135
  • 50
  • 484
  • 624
0

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
Debasis Das
  • 21
  • 4
0

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 ...

dnoeth
  • 54,996
  • 3
  • 29
  • 45