|
Archives of the TeradataForumMessage Posted: Mon, 04 Feb 2013 @ 20:31:25 GMT
Hi, I have the data like below: CREATE SET TABLE SAMPLES.test123 ,NO FALLBACK , NO BEFORE JOURNAL, NO AFTER JOURNAL, CHECKSUM = DEFAULT ( CAK VARCHAR(10) CHARACTER SET LATIN NOT CASESPECIFIC, DIAGCD VARCHAR(10) CHARACTER SET LATIN NOT CASESPECIFIC, QLFRCD VARCHAR(10) CHARACTER SET LATIN NOT CASESPECIFIC, ODRNO VARCHAR(10) CHARACTER SET LATIN NOT CASESPECIFIC) PRIMARY INDEX ( CAK ); CREATE SET TABLE SAMPLES.test1234 ,NO FALLBACK , NO BEFORE JOURNAL, NO AFTER JOURNAL, CHECKSUM = DEFAULT ( CAK VARCHAR(10) CHARACTER SET LATIN NOT CASESPECIFIC, DIAGCD VARCHAR(10) CHARACTER SET LATIN NOT CASESPECIFIC, QLFRCD VARCHAR(10) CHARACTER SET LATIN NOT CASESPECIFIC) PRIMARY INDEX ( CAK ); SELECT * FROM test123 SELECT * FROM test1234 Now my intention is to insert data in test123 when (test123.CAK <> test1234.CAK) OR (test123.DIAGCD <> test1234.DIAGCD) I have tried write a merge query like below: MERGE INTO test123 AS C1 USING ( SELECT CAK,DIAGCD,QLFRCD FROM test1234 ) AS C2 ON (C1.CAK = C2.CAK AND C1.DIAGCD = C2.DIAGCD) WHEN NOT MATCHED THEN INSERT (CAK, DIAGCD, QLFRCD, ODRNO) VALUES(C2.CAK,C2. DIAGCD, C2.QLFRCD, MAX(C1.ODRNO) OVER (PARTITION BY C1. CAK, C1.QLFRCD) + 1) Getting error 5977 : Ordered analytical function not allowed in MERGE-INTO. Please Suggest Regards, Koushik Chandra
| ||||||||||||||||||||||||||||||||||||||||||||||||
| ||||||||||||||||||||||||||||||||||||||||||||||||
Copyright 2016 - All Rights Reserved | ||||||||||||||||||||||||||||||||||||||||||||||||
Last Modified: 15 Jun 2023 | ||||||||||||||||||||||||||||||||||||||||||||||||