3706 syntax error

Database
Enthusiast

3706 syntax error

Hi Team,

when i run the following sql in Teradata SQL Assist, the sql execution is successful. but when i run the following SQL through script, the error message " *** Failure 3706 Syntax error: expected something between the word 'SCAN_RE

 QUEST_TS' and ','.

" is thrown.

INSERT
INTO ${PRD2_WORK_DB}.r_emmd_ItemScanHit
(
SCAN_ID
, SCAN_REQUEST_TS
, SCAN_RESPONSE_TS
, RESPONSE_STATUS_CD
, ID
, DSC
, CATEGORY_ID
, HOUSEHOLD_ID
, STORE_ID
, APPLICATION_ERROR_CD
, SCAN_REQUEST_URL
, SRC_EXTRACT_NM
)
SELECT
rfl.SCAN_ID
, (substring(rfl.SCAN_REQUEST_TS,7,4)||'-'||substring(rfl.SCAN_REQUEST_TS,1,2)||'-'||substring(rfl.SCAN_REQUEST_TS,4,2)||substring(rfl.SCAN_REQUEST_TS,11,9)) as SCAN_REQUEST_TS
, (substring(rfl.SCAN_RESPONSE_TS,7,4)||'-'||substring(rfl.SCAN_RESPONSE_TS,1,2)||'-'||substring(rfl.SCAN_RESPONSE_TS,4,2)||substring(rfl.SCAN_RESPONSE_TS,11,9)) as SCAN_RESPONSE_TS
, rfl.RESPONSE_STATUS_CD
, rfl.ID
, rfl.DSC
, rfl.CATEGORY_ID
, rfl.HOUSEHOLD_ID
, rfl.STORE_ID
, rfl.APPLICATION_ERROR_CD
, rfl.SCAN_REQUEST_URL
, 'scan_hits_1.csv'
FROM ${PRD2_WORK_DB}.r_emmd_ItemScanHit_fl rfl;

Can anyone help me to fix the issue?


Thanks in advance.

KK

2 REPLIES
Fan

Re: 3706 syntax error

I suppose you get the error when you run with Bteq script. The reason is you have used the wrong syntax of substring. In Bteq:

substring (string_value FROM 1 FOR 256 ))  or

substr (string_value, 1, 256)

so, if you replace all substring to substr it will work in Bteq.

Br,

Socola

Enthusiast

Re: 3706 syntax error

Replacing substring by substr works.

Thank you socola.