Archives of the TeradataForum

Message Posted: Tue, 08 Apr 2014 @ 14:53:00 GMT

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

Fred Pluebell states

I assume the following insert is Single Row and would therefore be managed by the PE and not the AMP.

     INSERT INTO REPORT_SUBSCRIPTION
     (
     -- SUBSCRIPTION_ID ,
     CUSTOMER_TYPE_CD,
     EXTL_EMAIL_ADDR_TX
     )
     VALUES (
     'RPAR'
     ,NEW EMAIL_ADDRESS_A01('myemail@tnt.com','my2email@tnt.com','')
     );

Why oh why oh why, therefore, would I get the

"7545 : Numbering for Identity Column SUBSCRIPTION_ID is over its limit"

when the Table is set up to allow up to 5 values but I've only got 3 rows in the Table :

     SEL * FROM REPORT_SUBSCRIPTION ;

     1        RPAR        ('myemail@tnt.com','my2email@tnt.com','')
     2        RP01        ('myemail@tnt.com','my2email@tnt.com','')
     3        RP02        ('myemail@tnt.com','my2email@tnt.com','')


     CREATE MULTISET TABLE REPORT_SUBSCRIPTION
          (
           SUBSCRIPTION_ID SMALLINT NOT NULL GENERATED ALWAYS AS IDENTITY
                (START WITH 1
                 INCREMENT BY 1
                 MINVALUE 1
                 MAXVALUE 5
                 NO CYCLE),
           CUSTOMER_TYPE_CD CHAR(4) CHARACTER SET UNICODE NOT CASESPECIFIC,
           EXTL_EMAIL_ADDR_TX SYSUDTLIB.EMAIL_ADDRESS_A01
     )
     UNIQUE PRIMARY INDEX REPORT_SUBSCRIPTION_UPI ( SUBSCRIPTION_ID );

All I can say is that these (test) rows were added yesterday, and today it's not working !

Oh, and to answer Todd's question, IdColBatchSize is set to 100,000

It's a pity, as it was ideal for what I was doing.

No matter, I can define it as INTEGER and do a SELECT MAX( SUBSCRIPTION_ID) every time - bit crap, but at least it will work !!

Regards

David Clough
Senior BI Database Designer
BI Competency Centre

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