This is related to running STRTOK after our Teradata 15.10 upgrade, this ran fine under 15.00

Database
Teradata Employee

This is related to running STRTOK after our Teradata 15.10 upgrade, this ran fine under 15.00

--SQL running
I have run into an issue in Teradata with a piece of code that I have been running weekly for the past year plus. The code is as follows (pardon the terrible formatting):

CREATE TABLE IDM_MARKETING_MACI.MA_CALL_EXTRA_BINDS, no fallback
AS
(
SELECT Q.CONNID AS QCONNID,A.CONNID

FROM(SELECT QFINITY.*

FROM (SELECT CTM.GROUP_NB

FROM CA_MARKETING_ANALYTICS.MA_CALL_CTM_RAW CTM

LEFT JOIN IDM_MARKETING_MACI.MA_CALL_LINKS4 L
ON CTM.GROUP_NB = L.CONNID

WHERE L.CONNID IS NULL

GROUP BY 1) CTM

INNER JOIN (SELECT ORIG_ANI,RECORDED_DT,USER_ID,EXTRACT(HOUR FROM TRIGGER_START_TIME)+1 AS HOUR_OF_DAY,
CAST(STRTOK(CONN_ID,',',1) AS VARCHAR(100)) AS CONNID

FROM STG_MQFT_DB.QFINITY_STG

WHERE RECORDED_DT >= '2015-01-01 00:00:00.000000'
AND ORIG_ANI <> ''
AND USER_ID IS NOT NULL
AND CONN_ID <> ''
AND CONN_ID IS NOT NULL

GROUP BY 1,2,3,4,5

UNION

(SELECT ORIG_ANI,RECORDED_DT,USER_ID,EXTRACT(HOUR FROM TRIGGER_START_TIME)+1 AS HOUR_OF_DAY,
CAST(STRTOK(CONN_ID,',',2) AS VARCHAR(100)) AS CONNID

FROM STG_MQFT_DB.QFINITY_STG

WHERE RECORDED_DT >= '2015-01-01 00:00:00.000000'
AND ORIG_ANI <> ''
AND USER_ID IS NOT NULL
AND CONN_ID <> ''
AND CONN_ID IS NOT NULL

GROUP BY 1,2,3,4,5)

UNION

(SELECT ORIG_ANI,RECORDED_DT,USER_ID,EXTRACT(HOUR FROM TRIGGER_START_TIME)+1 AS HOUR_OF_DAY,
CAST(STRTOK(CONN_ID,',',3) AS VARCHAR(100)) AS CONNID

FROM STG_MQFT_DB.QFINITY_STG

WHERE RECORDED_DT >= '2015-01-01 00:00:00.000000'
AND ORIG_ANI <> ''
AND USER_ID IS NOT NULL
AND CONN_ID <> ''
AND CONN_ID IS NOT NULL

GROUP BY 1,2,3,4,5)) QFINITY
ON CTM.GROUP_NB = QFINITY.CONNID) Q

INNER JOIN (SELECT C.CONNID,C.CALL_DT,C.ROUTED_AGENT_ID,C.ANI_NB,EXTRACT(HOUR FROM C.CALL_BEGIN_TS) AS HOUR_OF_DAY

FROM STG_MARL_DB.ADVANCED_ROUTING_LOG C

LEFT JOIN IDM_MARKETING_MACI.MA_CALL_ROUTE_INFO R
ON C.ROUTED_TO_OTHER = R.ROUTED_TO_OTHER

WHERE C.CALL_DT >= '2015-01-01 00:00:00.000000'
AND C.ORIG_CONNID IS NOT NULL
AND C.DESTINATION_DS = 'CSR' --only answered calls
AND C.CALL_SOURCE_DS IN ('Inbound','Transfer')
AND (C.PREV_BUSINESS_UNIT_DS NOT LIKE '%Sales%' OR C.ORIG_CONNID <> '<N/A>') --exclude weird case where ORIG_CONNID appears to be missing
AND C.BUSINESS_UNIT_DS IN ('Sales','Commercial','Financial')
AND R.MARKETING_COUNTED = 1
AND C.PREROUTE_ABANDON_FL = 0
AND C.LOB_DS = 'NSS'
AND C.CALL_TYPE_DS NOT LIKE '%Web Support%') A
ON Q.ORIG_ANI = A.ANI_NB
AND Q.USER_ID = A.ROUTED_AGENT_ID
AND Q.RECORDED_DT = A.CALL_DT

WHERE Q.HOUR_OF_DAY = A.HOUR_OF_DAY
OR Q.HOUR_OF_DAY-1 = A.HOUR_OF_DAY
)
WITH DATA

PRIMARY INDEX (QCONNID);

COLLECT STATISTICS ON IDM_MARKETING_MACI.MA_CALL_EXTRA_BINDS
INDEX (QCONNID);

--thoughts after the errors
Here is some pseudo-code that should help explain what I am doing – essentially, I am trying to create an equivalence between call IDs in different systems using other information about the call. The goal of that is to use this equivalence to tie more calls to results.

SELECT CALL ID FROM SOURCE 1, CALL ID FROM SOURCE 2

FROM (LIST OF CALL IDS NOT IN SOURCE 3 BUT NOT IN SOURCE 2 inner join(on call ID) LIST OF CALL IDS IN SOURCE 1) inner join(on all non-call ID info) (LIST OF CALL IDS IN SOURCE 2)

The problem I am running into is this:


Error which i am gettong:
CREATE TABLE FAILED.9134:STRTOK:Input string or Delim lenght is 0.OR Token parameter is no larger than 0.

 

Note : 

Can anyone let me know what is the reason for the above mentioned erroe and how to reslove it.

 

Best Regards,

Sunder

2 REPLIES
Senior Apprentice

Re: This is related to running STRTOK after our Teradata 15.10 upgrade, this ran fine under 15.00

Hi Sunder,

 

The basic STRTOK function works for me (15.10.1.1) - even allowing for some data to not have the numbered token (i.e. I've asked for the 7th token but some rows only contain 3).

 

Have you tried building this up? Run the first SELECT by itself. Does it run ok?

If not narrow down to the line of code that fails (presumably the STRTOK function).

 

Then add in the second SELECT, and so on.

 

HTH

Dave

Ward Analytics Ltd - information in motion
www: http://www.ward-analytics.com
Junior Contributor

Re: This is related to running STRTOK after our Teradata 15.10 upgrade, this ran fine under 15.00

Are there any empty strings in CONN_ID?

Don't know if this didn't fail before (it's as stupid error anyway as a single space doesn't fail), but as a workaround you can apply NULLIF(CONN_ID, '') or CASE WHEN CONN_ID = '' THEN ' ' ELSE CONN_ID END