Archives of the TeradataForum

Message Posted: Mon, 03 May 2004 @ 12:19:18 GMT

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

Victor Sokovin wrote:

It's really strange, as soon as the milliseconds add to more than 1 second:

(Nums is a table with consecutive integers, V2R05.01.00.06)

     sel
        interval '0:00:01.999' hour to second as i,
        n (format 'zzz'),
        i * n  as x,
        sum(i) over (order by n rows unbounded preceding) as cum_sum,
        mdiff(x,1,n)
     from nums
     where n <= 100
     order by n;

       *** Query completed. 100 rows found. 5 columns returned.
       *** Total elapsed time was 1 second.

                i    n                x       cum_sum     MDiff(x,1,n)
     ------------  ---  ---------------  ------------  ---------------
.......
      0:00:01.999   29      0:00:57.971   0:00:57.971      0:00:01.999
      0:00:01.999   30      0:00:59.970   0:00:59.970      0:00:01.999
      0:00:01.999   31      0:00:61.969   0:01:01.969      0:00:01.999
      0:00:01.999   32      0:00:63.968   0:01:03.968      0:00:01.999
      0:00:01.999   33      0:00:65.967   0:01:05.967      0:00:01.999
.......
      0:00:01.999   49      0:00:97.951   0:01:37.951      0:00:01.999
      0:00:01.999   50      0:00:99.950   0:01:39.950      0:00:01.999
      0:00:01.999   51      0:00:01.949   0:01:41.949      0:00:01.999
      0:00:01.999   52      0:00:03.948   0:01:43.948      0:00:01.999
.......
      0:00:01.999   59      0:00:17.941   0:01:57.941      0:00:01.999
      0:00:01.999   60      0:01:59.940   0:01:59.940      0:00:01.999
      0:00:01.999   61      0:01:61.939   0:02:01.939      0:00:01.999
      0:00:01.999   62      0:01:63.938   0:02:03.938      0:00:01.999
     ...

The Cumulative Sum is correct.

Especially strange:

- MDiff is always returning the same difference.

- If you calculate the difference between cum_sum and x it's always 0.

- But if you "QUALIFY cum_sum <> 0" then every row with n >=31 is returned.

Don't ask me why ;-)

Dieter

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