Archives of the TeradataForum

Message Posted: Tue, 20 Apr 2004 @ 20:36:28 GMT

	<Prev	Next>		<<First	<Prev	Next>	Last>>

<-- 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 non-aggregated 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

	<Prev	Next>		<<First	<Prev	Next>	Last>>