
Archives of the TeradataForumMessage Posted: Tue, 20 Apr 2004 @ 20:36:28 GMT
< Anonymously Posted: Tuesday, April 20, 2004 16:13 > The following is the scenario of the problem  TABLE 1  X Y Z ZZ A B C D E F a b c d 1 1 1 AB 1 1 1 1 1 1 u v u i 1 1 1 AC 1 1 2 1 1 2 u v x j 2 2 2 AB 2 2 2 2 2 2 k k e 3 2 2 2 DE 2 2 3 2 2 7 k e k e 3 3 3 BB 1 1 1 1 1 1 p n e f WHERE X,Y,Z,ZZ are part of PK A, B, C, D, E, F are Integer values a, b, c, d are attributes values for the rows. TABLE 2  X Y Z A B C D E F a b c d WHERE X, Y, Z are part of PK and there is no ZZ. A, B, C, D, E, F are Integer values a, b, c, d are attributes values for the rows. Data needs to inserted into Table 2 table from Table 1  X Y Z A B C D E F a b c d 1 1 1 2 2 3 2 2 3 u v u i 2 2 2 4 4 5 4 4 9 u v x j 3 3 3 1 1 1 1 1 1 k k e 3 In case there are multiple rows based on ZZ from Table 1, then insert using SUM(A), SUM(B), SUM(D), SUM(E), SUM(F) for those rows from Table 1 But the problem is to implement using SQL where the SQL should be able to SUM up the values for A, B.. ,F column if there are multiple ZZ in it. The query  INSERT INTO Table2 ( a, b, c, d, A, B, C, D, E, F, X, Y, Z ) SELECT a, b, c, d, SUM (A), SUM (B), SUM (C), SUM (D), SUM (E), SUM (F), X, Y, Z FROM Table1 group by X, Y, Z, ZZ; does not work because a, b, c are part of nonaggregated values. The query needs to be executed from a proc and the source table has 10m rows. FOR Cursor processing will go on for ever for such count. A partial Insert could be done using temp tables, but I am looking for cleaner method. Thanks
 
 
Copyright 2016  All Rights Reserved  
Last Modified: 28 Jun 2020  