Archives of the TeradataForum

Message Posted: Mon, 01 Nov 2010 @ 18:23:17 GMT

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

Hello Guys,

I am working on some code where I need to add a sequence number with the input record (taken from a table A) and load it in table B. Both tables are SET-UPI.

For example: I have a "Table A" with following data:

     Field1 Field2 Field3 Field4
     1        2        3        5
     1        2        3        4

I want to load data from Table A to B (replica of A with an extra column added for Row_Number() function) and the result must be

     Row Nbr Field1 Field2 Field3 Field4
     1                  1        2        3        5
     2                  1        2        3        4

SQL is:

     Insert into Table-B
     (
             Row_nbr,
             ,Field1
             ,Field2
             ,Field3
             ,Field4
     )
     Select
             Row_number() Over (Order By Field1, Field2, Field3)
             ,Field1
             ,Field2
             ,Field3
             ,Field4
     From    Table-A;

During testing I loaded data from A to B several times but sometimes I got result-1 and get result-2:

Result-1

     Row Nbr         Field1 Field2 Field3 Field4
     1                  1        2        3        5
     2                  1        2        3        4

Result-2

     Row Nbr         Field1 Field2 Field3 Field4
     1                  1        2        3        4
     2                  1        2        3        5

Anyone has any idea why is it so that it is not picking the first record first always?

I always want result-1 i.e. the order should be same as in Table A.

Is there any way to do this?

Thanks

Lakhwant

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