Archives of the TeradataForum

Message Posted: Fri, 17 Dec 2004 @ 08:33:47 GMT

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

When "cycle" and "start with" option is specified, and the values reached to the "maxvalue/minvalue", the next value will become a minimum/maximum value of its data type if no minvalue/maxvalue is specified. On DB2, the new value will be the one which is specified in 'start with' option.

Is this the specification of Teradata ? Which one is the specification of SQL99 ?

(Example) On DB2, following example retrieves the same result set.

     $ create table mytbl1 (id int generated always as identity (start with
     -1 increment by 1 maxvalue 2 cycle minvalue -1), c1 int)

     $ create table mytbl2 (id int generated always as identity (start with
     -1 increment by 1 maxvalue 2 cycle), c1 int)

     $ insert into mytbl1 (c1) values (1)
     $ insert into mytbl1 (c1) values (2)
     $ insert into mytbl1 (c1) values (3)
     $ insert into mytbl1 (c1) values (4)
     $ insert into mytbl1 (c1) values (5)

     $ insert into mytbl2 (c1) values (1)
     $ insert into mytbl2 (c1) values (2)
     $ insert into mytbl2 (c1) values (3)
     $ insert into mytbl2 (c1) values (4)
     $ insert into mytbl2 (c1) values (5)

     $ select * from mytbl1 order by c1

     id          c1
     ----------- -----------
              -1           1
               0           2
               1           3
               2           4
              -1           5

       5 record(s) selected.

     $ select * from mytbl2 order by c1

     id          c1
     ----------- -----------
              -1           1
               0           2
               1           3
               2           4
     -2147483647           5

       5 record(s) selected.

On DB2, the last "-2147483647" will be "-1".

Tadaaki Kawamura

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