MERGE INTO table1 as a
USING table2 as b
ON a.date = b.date AND a.id = b.id
WHEN MATCHED THEN
UPDATE SET col2 = CAST(a.col2 as VARCHAR(3)) ||','|| CAST(b.col2 as VARCHAR(3))
WHEN NOT MATCHED THEN
INSERT (id, col2, date)
VALUES (b.id, b.col2, b.date);
Have you done the part:
select CAST(a.col2 as VARCHAR(3)) ||','|| CAST(b.col2 as VARCHAR(3)) from ......
Can it be because of width anomoly of col2=.....???
In other databases, say
SELECT CAST(a.EMPNO as VARCHAR(3)) ||','|| CAST(A.DEPTNO as VARCHAR(3)) FROM EMP A--- will error out .
The solution is increase the width:
SELECT CAST(a.EMPNO as VARCHAR(10)) ||','|| CAST(A.DEPTNO as VARCHAR(10)) FROM EMP A
Yes, I've tried that and it works. I believe that the SET expression in MERGE INTO doesn't support concatenation.
Agree with Dieter. I cannot see in the link provided, that it does not support concatenation. Also , if it does not support such concatenation , then it is a limitation of DB. Maybe you can look again at datatype and a.col2, b.col2 and query with matching conditions a.
a.id = b.id.
String truncation is not considered an error in Teradata mode. My guess is the concatenated string simply doesn't fit in the target column, which makes it appear as if no update is occurring.