Merge Statement Errors - MERGE Failed. 3707

Database

Merge Statement Errors - MERGE Failed. 3707

We are having challenges with a Merge statement. The SQL we are using for the source data works fine when run as a select statement, but for some reason will not run when within the merge statement - the error message is :

MERGE Failed. 3707: Syntax error, expected something like a name or a Unicode delimited identifier or a 'ROWID' keyword between ',' and the 'WHEN' keyword.
Output directed to Answer window

Below is the sql - are we missing something?

MERGE INTO BIDW_TABLES.Fact_eL_CertAssignment as tgt
USING
(

SELECT
cast(substr(c.certid,6,character_length(c.certid)) as integer) as S_Cert_ID,
cast(c.PercentageComplete as float) as S_PercentComplete,
cast(c.studentid as integer) as S_Person_ID
from
ODS_TABLES.elearningCertCurAssignment C
where c.studentid = '985710'
) as src
ON
tgt.Person_ID = src.S_Person_ID and tgt.Cert_ID = src.S_Cert_ID

WHEN MATCHED THEN
UPDATE SET
tgt.PercentComplete = src.S_PercentComplete,

WHEN NOT
MATCHED THEN
INSERT
( Cert_ID,
PercentComplete,
Person_ID
)
VALUES
(src.S_Cert_ID,
src.S_PercentComplete,
src.S_Person_ID
)

);
2 REPLIES
Senior Apprentice

Re: Merge Statement Errors - MERGE Failed. 3707

Remove the comma after

tgt.PercentComplete = src.S_PercentComplete,

Dieter

N/A

Re: Merge Statement Errors - MERGE Failed. 3707

Thanks Dnoeth.. Actually we are attempting to create the following macro below.. (using a single row for testing)

The SQL works fine but the Create Macro is not successful.. The error reported using SQL assistant is "Create Macro Failed 3706: Syntax error: expected something between the word 'certid' and '.'  Verified that the user account has full permissions for to teradata object creation(macros/tables etc etc)

CREATE

MACRO BIDW_TABLES.MCR_Fact_eL_CertAssignment

AS

(

MERGE INTO BIDW_TABLES.Fact_eL_CertAssignment as Target

USING

(

SELECT cast(substring(c.certid,6,character_length(c.certid)) as integer) as S_Cert_ID,

t.CertType_ID as S_CertType_ID,

l.CertAssignStatus_ID as S_CertAssignStatus_ID,

cast(c.PercentageComplete as float) as S_PercentComplete,

zeroifnull(e.DATE_ID) as S_CertExpiredDate_ID,

zeroifnull(a.DATE_ID) as S_CertAcquiredDate_ID,

zeroifnull(x.DATE_ID) as S_CertAssignDate_ID,

cast(c.studentid as integer) as S_Person_ID

from

ODS_TABLES.elearningCertCurAssignment C

inner join ODS_TABLES.elearningCertification lc

on lc.certid = c.certid

inner join ODS_TABLES.LU_CertAssignStatus l

on c.statuscode = l.CertAssignStatus_ID

inner join ODS_TABLES.LU_CertType t

on t.CertType_ID = lc.Certtype

LEFT OUTER JOIN BIDW_TABLES.LK_DATE e

ON e.DATE_DT = C.expireson

LEFT OUTER JOIN BIDW_TABLES.LK_DATE a

ON a.DATE_DT = c.acquiredon

LEFT OUTER JOIN BIDW_TABLES.LK_DATE x

ON x.DATE_DT = c.assigndate

where

c.studentid = '985710'-- single useras Source

ON

Person_ID = Source.S_Person_ID and

Cert_ID = Source.S_Cert_ID

WHEN MATCHED THEN

UPDATE SET

CertAssignStatus_ID = Source.S_CertAssignStatus_ID,

PercentComplete = Source.S_PercentComplete,

CertExpiredDate_ID = Source.S_CertExpiredDate_ID,

CertAcquiredDate_ID= Source.S_CertAcquiredDate_ID,

CertAssignDate_ID = Source.S_CertAssignDate_ID

WHEN NOT MATCHED THEN

INSERT

( Cert_ID,

CertType_ID,

CertAssignStatus_ID,

PercentComplete,

CertExpiredDate_ID,

CertAcquiredDate_ID,

CertAssignDate_ID,

Person_ID

)

VALUES

(Source.S_Cert_ID,

Source.S_CertType_ID,

Source.S_CertAssignStatus_ID,

Source.S_PercentComplete,

Source.S_CertExpiredDate_ID,

Source.S_CertAcquiredDate_ID,

Source.S_CertAssignDate_ID,

Source.S_Person_ID

)

LOGGING ERRORS WITH LIMIT OF 100;

);

Appreciate the insight ..