ERROR: expected something like a name or a Unicode delimited identifier or an 'UDFCALLNAME' keyword

Database
Highlighted
Enthusiast

ERROR: expected something like a name or a Unicode delimited identifier or an 'UDFCALLNAME' keyword

Hi,

I am running the codes:

 

create table &gbUserDataBase..&gbTablePrefix._OTHER_EXC7TMP as
(SELECT *
FROM
(SELECT DISTINCT
A.CLNT_NO
,' ' as HOUSEHOLD
,' ' as TELEPHONE
,CAST('3015-01-04' AS DATE ) AS LAST_DAY
,'BusCard_Only_Pers' AS REASON
FROM D3CV12A.ACCT_CRD_OWN_DLY A
INNER JOIN D3CV12A.DLY_FULL_PORTFOLIO BDLY
ON A.ACCT_NO = BDLY.ACCT_NO AND
A.SRVC_ID = BDLY.SRVC_ID
INNER JOIN DDWV01.RB_CLNT_DLY AS C
ON C.SNAP_DT = A.SNAP_DT AND
A.CLNT_NO = C.CLNT_NO
LEFT JOIN D3CV12A.PRTFOL_MIX_HIST D
ON D.MTH_END_DT = (SELECT ME_DT
FROM D3CV12A.LOAD_CNTRL) AND
A.CLNT_NO = D.CLNT_NO
LEFT JOIN DG6V01.CLNT_DERIV_DTA AS E
ON A.CLNT_NO = E.CLNT_NO
WHERE A.SNAP_DT = (SELECT MAX(SNAP_DT)
FROM D3CV12A.SNAP_DATES_DLY
WHERE TAB_NM = 'TACCT_CRD_OWN_DLY') AND
A.CARD_STS IN ('A', ' ') AND
BDLY.DT_RECORD_EXT = (SELECT MAX(DT_RECORD_EXT)
FROM D3CV12A.DLY_FULL_PORTFOLIO) AND
BDLY.VISA_PROD_CD IN ('CLX', 'CPX', 'CXP', 'GLX', 'GXP', 'UXP','MC5','MC6') AND
BDLY.STATUS IN ('OPEN','COLL') AND
C.CLNT_TYP = '1' AND
((D.TOT_PROD_CNT = 1 AND A.RELATIONSHIP_CD IN ('P', 'A')) OR
(D.TOT_PROD_CNT = 0 AND A.RELATIONSHIP_CD IN ('2', '3'))
) AND
E.CRD_CLNT_ONLY_IND = 'Y'

UNION

SELECT DISTINCT (A.CLNT_NO)
,' ' as HOUSEHOLD
,' ' as TELEPHONE
,CAST('3015-01-04' AS DATE ) AS LAST_DAY
,'BusCard_Only_Pers' AS REASON
FROM D3CV12A.ACCT_CARD_OWN A
INNER JOIN D3CV12A.VISA_ACCT_MTH_ST_R B
ON A.DT_RECORD_EXT = B.ME_DT AND
A.ACCT_NO = B.ACCT_NO
INNER JOIN D3CV12A.PERS_CLNT C
ON A.DT_RECORD_EXT = C.DT_RECORD_EXT AND
A.CLNT_NO = C.CLNT_NO
LEFT JOIN D3CV12A.PRTFOL_MIX_HIST D
ON D.MTH_END_DT = (SELECT ME_DT
FROM D3CV12A.LOAD_CNTRL) AND
A.CLNT_NO = D.CLNT_NO
LEFT JOIN DG6V01.CLNT_DERIV_DTA AS E
ON A.CLNT_NO = E.CLNT_NO
WHERE A.DT_RECORD_EXT =(SELECT ME_DT
FROM D3CV12A.LOAD_CNTRL) AND
A.VISA_PROD_CD IN ('CLX', 'CPX', 'CXP', 'GLX', 'GXP', 'UXP','MC5','MC6') AND
A.CARD_STS IN ('A', ' ') AND
B.DT_CARD_EXPIRY >= A.DT_RECORD_EXT AND
B.STATUS IN ('OPEN','COLL') AND
C.CLNT_TP = '1' AND
((D.TOT_PROD_CNT = 1 AND A.RELATIONSHIP_CD IN ('P', 'A')) OR
(D.TOT_PROD_CNT = 0 AND A.RELATIONSHIP_CD IN ('2', '3'))
) AND
E.CRD_CLNT_ONLY_IND = 'Y'
)
WHERE qualify row_number() over (partition by CLNT_NO order by CLNT_NO) = 1
) with data primary index (clnt_no)

;

 

but getting an ERROR:

ERROR: Teradata execute: Syntax error, expected something like a name or a Unicode delimited identifier or an 'UDFCALLNAME' keyword
between ')' and the 'WHERE' keyword.

 

I want to de-dup the records by the key CLNT_NO (i.e. any duplicate rows with the same CLNT_NO will be eliminated) therefore I specified:

'WHERE qualify row_number() over (partition by CLNT_NO order by CLNT_NO) = 1'

 

Can someone pointing me the error and how to correct it?

 

Thanks

Stephen

 

3 REPLIES
Senior Apprentice

Re: ERROR: expected something like a name or a Unicode delimited identifier or an 'UDFCALLNAME' keyw

Hi Stephen,

 

Your problem is with the "where qualify". For this sql remove the WHERE word, just use QUALIFY.

 

Cheers,

Dave

 

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

Re: ERROR: expected something like a name or a Unicode delimited identifier or an 'UDFCALLNAME' keyw

Hi Dave,

 

Not working:

3707: Syntax error, expected something like a name or a Unicode delimited identifier or an 'UDFCALLNAME' keyword between ')' and the 'qualify' keyword.

 

And here's my query:

 

create table DDWUTDC.qjk8snk_OTHER_EXC7TMP as
(SELECT *
FROM
(SELECT DISTINCT
A.CLNT_NO
,' ' as HOUSEHOLD
,' ' as TELEPHONE
,CAST('3015-01-04' AS DATE ) AS LAST_DAY
,'BusCard_Only_Pers' AS REASON
FROM D3CV12A.ACCT_CRD_OWN_DLY A
INNER JOIN D3CV12A.DLY_FULL_PORTFOLIO BDLY
ON A.ACCT_NO = BDLY.ACCT_NO AND
A.SRVC_ID = BDLY.SRVC_ID
INNER JOIN DDWV01.RB_CLNT_DLY AS C
ON C.SNAP_DT = A.SNAP_DT AND
A.CLNT_NO = C.CLNT_NO
LEFT JOIN D3CV12A.PRTFOL_MIX_HIST D
ON D.MTH_END_DT = (SELECT ME_DT
FROM D3CV12A.LOAD_CNTRL) AND
A.CLNT_NO = D.CLNT_NO
LEFT JOIN DG6V01.CLNT_DERIV_DTA AS E
ON A.CLNT_NO = E.CLNT_NO
WHERE A.SNAP_DT = (SELECT MAX(SNAP_DT)
FROM D3CV12A.SNAP_DATES_DLY
WHERE TAB_NM = 'TACCT_CRD_OWN_DLY') AND
A.CARD_STS IN ('A', ' ') AND
BDLY.DT_RECORD_EXT = (SELECT MAX(DT_RECORD_EXT)
FROM D3CV12A.DLY_FULL_PORTFOLIO) AND
BDLY.VISA_PROD_CD IN ('CLX', 'CPX', 'CXP', 'GLX', 'GXP', 'UXP','MC5','MC6') AND
BDLY.STATUS IN ('OPEN','COLL') AND
C.CLNT_TYP = '1' AND
((D.TOT_PROD_CNT = 1 AND A.RELATIONSHIP_CD IN ('P', 'A')) OR
(D.TOT_PROD_CNT = 0 AND A.RELATIONSHIP_CD IN ('2', '3'))
) AND
E.CRD_CLNT_ONLY_IND = 'Y'

UNION

SELECT DISTINCT (A.CLNT_NO)
,' ' as HOUSEHOLD
,' ' as TELEPHONE
,CAST('3015-01-04' AS DATE ) AS LAST_DAY
,'BusCard_Only_Pers' AS REASON
FROM D3CV12A.ACCT_CARD_OWN A
INNER JOIN D3CV12A.VISA_ACCT_MTH_ST_R B
ON A.DT_RECORD_EXT = B.ME_DT AND
A.ACCT_NO = B.ACCT_NO
INNER JOIN D3CV12A.PERS_CLNT C
ON A.DT_RECORD_EXT = C.DT_RECORD_EXT AND
A.CLNT_NO = C.CLNT_NO
LEFT JOIN D3CV12A.PRTFOL_MIX_HIST D
ON D.MTH_END_DT = (SELECT ME_DT
FROM D3CV12A.LOAD_CNTRL) AND
A.CLNT_NO = D.CLNT_NO
LEFT JOIN DG6V01.CLNT_DERIV_DTA AS E
ON A.CLNT_NO = E.CLNT_NO
WHERE A.DT_RECORD_EXT =(SELECT ME_DT
FROM D3CV12A.LOAD_CNTRL) AND
A.VISA_PROD_CD IN ('CLX', 'CPX', 'CXP', 'GLX', 'GXP', 'UXP','MC5','MC6') AND
A.CARD_STS IN ('A', ' ') AND
B.DT_CARD_EXPIRY >= A.DT_RECORD_EXT AND
B.STATUS IN ('OPEN','COLL') AND
C.CLNT_TP = '1' AND
((D.TOT_PROD_CNT = 1 AND A.RELATIONSHIP_CD IN ('P', 'A')) OR
(D.TOT_PROD_CNT = 0 AND A.RELATIONSHIP_CD IN ('2', '3'))
) AND
E.CRD_CLNT_ONLY_IND = 'Y'
)
qualify row_number() over (partition by CLNT_NO order by CLNT_NO) = 1
) with data primary index (clnt_no);

 

Please advise.

 

Thanks

Stephen

Junior Contributor

Re: ERROR: expected something like a name or a Unicode delimited identifier or an 'UDFCALLNAME' keyw

Teradata & Standard SQL require an alias for a Derived Table (Oracle doesn't), simply add as bla before the qualify