problem while using coalesce function in insert stmt in tpt script

Tools & Utilities

problem while using coalesce function in insert stmt in tpt script

while executing below step in tpt i am getting below error.can you please suggest me on this.

STEP test_MACRO_EXE4
(
APPLY
CASE WHEN (REC_TYP_ID<>'1' and REC_TYP_ID <>'2' and REC_TYP_ID <>'3' or TLOG_VER_ID <> '2')
then
('INSERT INTO DEVSTAGE.DT_STH_DATA_N (
REC_TYP
,ERR_REC
,REC_CRT_TS
)
VALUES (
:TLOG_REC_TYP_ID,
COALESCE(:TLOG_VER_ID,'') || ' | ' || COALESCE(:TLOG_REC_TYP_ID,'') || ' | ' || COALESCE(:CHN_ID,'') || ' | ' || COALESCE(:LOC_ID,''),
,CURRENT_TIMESTAMP(6));')
end
TO OPERATOR (
UPDATE_OPERATOR [1]
)
SELECT * FROM OPERATOR (MQ_READER [1]));

);

 

TPT_INFRA: TPT04024: Error: Line 124 of Job Script File 'sample_test2.tpt':
Single pipe character ('|') encountered where double pipe expected.

 

 

1 REPLY
Teradata Employee

Re: problem while using coalesce function in insert stmt in tpt script

Note that the INSERT statement is itself provided as a string literal (which is correct), so the quotes around literals within the string must be escaped (doubled):

'INSERT INTO DEVSTAGE.DT_STH_DATA_N (
REC_TYP
,ERR_REC
,REC_CRT_TS
)
VALUES (
:TLOG_REC_TYP_ID,
COALESCE(:TLOG_VER_ID,'''') || ''|'' || COALESCE(:TLOG_REC_TYP_ID,'''') || ''|'' || COALESCE(:CHN_ID,'''') || ''|'' || COALESCE(:LOC_ID,''''),
,CURRENT_TIMESTAMP(6));')