Archives of the TeradataForum

Message Posted: Thu, 18 Jan 2001 @ 20:39:02 GMT

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

You could also try something like this.

     select csum (1, col1, col2, col3), col1, col2, col3
     from csum_test;

You can try this query against the following test data.

     create table csum_test (col1 integer, col2 integer, col3 integer)
     unique primary index (col1, col2, col3);

// NB: You can use any index you like on the above table, it doesn't have to be as I have specified and it doesn't have to be unique either.

     insert into csum_test (1,2,3);
     insert into csum_test (1,1,3);
     insert into csum_test (2,1,3);
     insert into csum_test (2,1,2);
     insert into csum_test (2,1,1);

     With the following result:
     Csum    col1    col2    col3
     1          1        1      3
     2          1        2      3
     3          2        1      1
     4          2        1      2
     5          2        1      3

Of course if you add new values into the middle of the mix, (eg. 1,2,4) you will get different results for the csum column but I think you said you didn't need to be able to regenerate the same set of values each time. I wasn't clear what you were saying about the following 6 runs. It sounds like you need to continue the csum value from the previous days - in my example the next ID would be 6? I have also done this, with a correlated sub query. Basically, the sub query figures out what the current highest ID is (in my example 5), then it adds this value to the csum values being generated in the outer query.

BTW the CSUM function was introduced on V2R3 so that above works on V2R3 or later. If you have V2R2 or earlier - then upgrade, but in the mean time, one of the other suggestions should also work. I believe that this technique of key generation has been introduced into the Advanced SQL training course, so you might want to consider that training as well for this and other neat ideas. And no I am not in sales so I don't get any commission!

I hope this works for you.

Glenn McCall
NCR - San Diego

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