Archives of the TeradataForum

Message Posted: Thu, 14 Dec 2006 @ 17:31:10 GMT

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

<-- Anonymously Posted: Thursday, December 14, 2006 12:33 -->

Hi:

I'd like to widen a column length with the ALTER TABLE statement (instead of creating a new table and doing Insert / Select). The manual says it is valid (See Page 1-48 of the SQL Reference: Data Definition Statements V2R5.1 Manual). Page 1-49 says only that you cannot decrease the length. However, Page 1-50 says "Increasing the length does not change the current format, instead you should consider including a new FORMAT phrase in the statement." Page 1-48 also says "Changes Hmmmm...

I KNOW you can do this as long as it is going from smaller to larger. I've seen it. Perhaps it is non-published? Hmmmm...

     alter table UCVMart.LTC_POLICIES_mj
     modify SERVICING_AGENT_STATUS_DE VARCHAR (255) ;

OR maybe

     alter table UCVMart.LTC_POLICIES_mj
     add SERVICING_AGENT_STATUS_DE VARCHAR (255) ;

currently it is

     SERVICING_AGENT_STATUS_DE VARCHAR(10) CHARACTER SET UNICODE NOT CASESPECIFIC,
     ..

Here is the table definition...

     CREATE SET TABLE UCVMart.LTC_POLICIES_mj ,NO FALLBACK ,
          NO BEFORE JOURNAL,
          NO AFTER JOURNAL,
          CHECKSUM = DEFAULT
          (
           POLICY_KEY_ID INTEGER,
           FROM_DT DATE FORMAT 'YY/MM/DD',
           THRU_DT DATE FORMAT 'YY/MM/DD',
           POLICY_NR VARCHAR(18) CHARACTER SET LATIN NOT CASESPECIFIC,
           POLICY_TYPE VARCHAR(255) CHARACTER SET LATIN NOT CASESPECIFIC,
           PLAN_CD VARCHAR(20) CHARACTER SET LATIN NOT CASESPECIFIC,
           PLAN_TYPE VARCHAR(255) CHARACTER SET LATIN NOT CASESPECIFIC,
           BILLING_FREQ_CD CHAR(3) CHARACTER SET LATIN NOT CASESPECIFIC,
           CURRENT_PREM_MODE_CD CHAR(3) CHARACTER SET LATIN NOT CASESPECIFIC,

           BILLING_FREQ_DE VARCHAR(255) CHARACTER SET LATIN NOT CASESPECIFIC,

           CURRENT_PREM_MODE_DE VARCHAR(255) CHARACTER SET LATIN NOT CASESPECIFIC,
           DAILY_BENEFIT DECIMAL(3,0),
           TOTAL_MAX_BEN_AM DECIMAL(9,0),
           POL_MAX_CAREGIVER DECIMAL(9,0),
           POL_MAX_ALTERNATIVE INTEGER,
           ELIM_PRD_CD INTEGER,
           ELIM_PRD_DE VARCHAR(255) CHARACTER SET LATIN NOT CASESPECIFIC,
           BENEFIT_TERM_CD VARCHAR(20) CHARACTER SET LATIN NOT CASESPECIFIC,
           BENEFIT_TERM_DE VARCHAR(255) CHARACTER SET LATIN NOT CASESPECIFIC,

           ANNUAL_PREM_AM DECIMAL(11,2),
           MODAL_PREM_AM DECIMAL(11,2),
           SEMI_ANNUAL_PREMIUM_AM DECIMAL(11,2),
           QUARTERLY_PREMIUM_AM DECIMAL(11,2),
           APP_PREMIUM_AM DECIMAL(11,2),
           APPLICATION_STATE_CD CHAR(3) CHARACTER SET LATIN NOT CASESPECIFIC,

           ANNV_MONTH_DAY_TX CHAR(5) CHARACTER SET LATIN NOT CASESPECIFIC,
           LAST_PREM_PAID_AM DECIMAL(11,2),
           PREMIUM_TYPE_DE VARCHAR(255) CHARACTER SET LATIN NOT CASESPECIFIC,

           SPECIAL_PLAN_DISC_PC DECIMAL(3,2),
           SERVICING_AGENT_CD CHAR(10) CHARACTER SET LATIN NOT CASESPECIFIC,
           SERVICING_AGENT_NM VARCHAR(134) CHARACTER SET LATIN NOT CASESPECIFIC,
           SERVICING_AGENT_STATUS_CD VARCHAR(1) CHARACTER SET UNICODE NOT CASESPECIFIC,
           SERVICING_AGENT_STATUS_DE VARCHAR(10) CHARACTER SET UNICODE NOT CASESPECIFIC,
           SERVICING_AGENCY_CD CHAR(3) CHARACTER SET LATIN NOT CASESPECIFIC,
           SERVICING_AGENCY_NM_1 VARCHAR(30) CHARACTER SET LATIN NOT CASESPECIFIC,
           UNDERWRITING_CLASS_CD CHAR(3) CHARACTER SET LATIN NOT CASESPECIFIC,
           UNDERWRITING_CLASS_DE VARCHAR(255) CHARACTER SET LATIN NOT CASESPECIFIC,
           BILLING_NR VARCHAR(20) CHARACTER SET LATIN NOT CASESPECIFIC,
           BATCH_BILL_DISC_PC DECIMAL(5,2),
           APP_GOM_CONTROL_NR VARCHAR(20) CHARACTER SET LATIN NOT CASESPECIFIC)
     PRIMARY INDEX ( POLICY_KEY_ID );

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