MERGE INTO and CONCATENATION

Database
Enthusiast

MERGE INTO and CONCATENATION

So, I'm not sure why this statement isn't working properly.

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);

I'm trying to concatenate column 2 when both column 1 and 3 match from each table.  The statement executes with no errors, however, it's as if the "WHEN MATCHED" statement is ignored as there is no concatenation, but several id/date match.  I'm not sure why it isn't working.

Any suggestions?

Tags (2)
7 REPLIES
Enthusiast

Re: MERGE INTO and CONCATENATION

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

Enthusiast

Re: MERGE INTO and CONCATENATION

Yes, I've tried that and it works.  I believe that the SET expression in MERGE INTO doesn't support concatenation. 

http://www.info.teradata.com/htmlpubs/DB_TTU_14_00/index.html#page/SQL_Reference/B035_1146_111A/ch03...

Senior Apprentice

Re: MERGE INTO and CONCATENATION

Of course MERGE supports concat.

There must be something else, what's the datatype of those col2?

Enthusiast

Re: MERGE INTO and 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.date = b.date AND a.id = b.id.

Teradata Employee

Re: MERGE INTO and CONCATENATION

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.

Enthusiast

Re: MERGE INTO and CONCATENATION

Hi Cloud36,

Maybe you can share the descriptions of yout table1 ,table2 and  sample data maybe too. 

Cheers,

Teradata Employee

Re: MERGE INTO and CONCATENATION

Increase the data-type of col2 in target table.  It should resolve the issue.