Home Page for the TeradataForum
 

Archives of the TeradataForum

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


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


Subj:   Specification of 'Cycle' and 'Start with' option of identity column
 
From:   Tadaaki Kawamura

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>>  
 
 
 
 
 
 
 
 
  
  Top Home Privacy Feedback  
 
 
Copyright for the TeradataForum (TDATA-L), Manta BlueSky    
Copyright 2016 - All Rights Reserved    
Last Modified: 27 Dec 2016