All Forums Database
cloud36 5 posts Joined 04/14
02 Apr 2014
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?

Raja_KT 1246 posts Joined 07/09
02 Apr 2014

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

Raja K Thaw
My wiki: http://en.wikipedia.org/wiki/User:Kt_raj1
Street Children suffer not by their fault. We can help them if we want.

cloud36 5 posts Joined 04/14
03 Apr 2014

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.034.296.html#ww17534833

dnoeth 4628 posts Joined 11/04
03 Apr 2014

Of course MERGE supports concat.
There must be something else, what's the datatype of those col2?

Dieter

Raja_KT 1246 posts Joined 07/09
03 Apr 2014

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.

Raja K Thaw
My wiki: http://en.wikipedia.org/wiki/User:Kt_raj1
Street Children suffer not by their fault. We can help them if we want.

Fred 1096 posts Joined 08/04
04 Apr 2014

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.

Raja_KT 1246 posts Joined 07/09
04 Apr 2014

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

Raja K Thaw
My wiki: http://en.wikipedia.org/wiki/User:Kt_raj1
Street Children suffer not by their fault. We can help them if we want.

Adeel Chaudhry 773 posts Joined 04/08
08 Apr 2014

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

-- If you are stuck at something .... consider it an opportunity to think anew.

You must sign in to leave a comment.