Archives of the TeradataForum

Message Posted: Thu, 18 Jan 2001 @ 14:15:02 GMT

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

Claybourne,

I'm not sure I've understood correctly about the different 'sets', but to convert three values into a single unique value can be done using the CSUM function (I assume you're on v2r3 +) - or indeed to simply generate unique values;

     Select csum(1,col1,col2,col3)
          ,< other columns >
     From < table >
     Where ...

will generate a unique number for every row in the answer set. It doesn't matter what the data types of the 'input columns' (col1, col2, col3) are. If there is no required relationship between the three existing integer values and the new unique integer, then simply using "CSUM(1,1)" will still generate unique numbers.

You could probably use this as a basis for what you want to achieve.

wrt the 'sets': I think each 'set' represents a days worth of data. What you might want to do then is to use the number of rows that exist in the target table (i.e. the one you're adding to every night) currently as the lower value for the newly generated number. Something like;

     Select csum(1,a.col1,a.col2,a.col3) + b.rowcount
          ,< other columns >
     From < source-table > a
            ,(Select count(*)
              from < target-table >)
             as b (rowcount)
     Where ...

So if on the first night of the week you add 1000 rows to they'll be numbered 1 - 1000, but on the second night the rows will be numbered starting at 1001, so they'll have unique values.

Regards,

Dave

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