CREATE TABLE AS STATEMENT ERROR

Database
Enthusiast

CREATE TABLE AS STATEMENT ERROR

Apparent anomaly in the functioning of the CREATE TABLE AS statement. In the following SQL, the B_TEST table DDL gets created with double-quotes around the CHECK col in 2 cases:

CREATE MULTISET TABLE A_TEST

(

COL_1 VARCHAR(120) CHARACTER SET LATIN CASESPECIFIC NOT NULL ,

COL_IND_1 VARCHAR(40) CHARACTER SET LATIN CASESPECIFIC NOT NULL,

COL_CHECK VARCHAR(200) CHARACTER SET LATIN CASESPECIFIC,

CHECK ( (INDEX(COL_CHECK ,' ' ) ) = 0 ),

CHECK ( (INDEX(COL_CHECK ,')' ) ) = 0 ),

CHECK ( (INDEX(COL_CHECK ,'(' ) ) = 0 ))

PRIMARY INDEX TEST_NUPI ( COL_IND_1 );

CREATE TABLE B_TEST AS A_TEST WITH NO DATA;

The expected result is for the DDL to be identical.

But the exepcted result is the DDL get created with 2 double quotes around the CHECK col in 2 cases.

CREATE MULTISET TABLE b_test ,NO FALLBACK ,

     NO BEFORE JOURNAL,

     NO AFTER JOURNAL,

     CHECKSUM = DEFAULT,

     DEFAULT MERGEBLOCKRATIO

     (

      COL_1 VARCHAR(120) CHARACTER SET LATIN CASESPECIFIC NOT NULL,

      COL_IND_1 VARCHAR(40) CHARACTER SET LATIN CASESPECIFIC NOT NULL,

      COL_CHECK VARCHAR(200) CHARACTER SET LATIN CASESPECIFIC,

      CHECK ( (INDEX(COL_CHECK ,' '))=  0  ),

      CHECK ( (INDEX("COL_CHECK" ,')'))=  0  ),

      CHECK ( (INDEX("COL_CHECK" ,'('))=  0  ))

PRIMARY INDEX TEST_NUPI ( COL_IND_1 );

Any solution for this?

3 REPLIES
Supporter

Re: CREATE TABLE AS STATEMENT ERROR

OK, there is this difference of double quoted names - but what is your concern?

"colname" is still refering to the colname...

So there is no difference in the actual table definition...

Enthusiast

Re: CREATE TABLE AS STATEMENT ERROR

There is no concerns.

My question is why  the second & third column showing as reserved word?

Teradata Employee

Re: CREATE TABLE AS STATEMENT ERROR

It's probably a Teradata Database bug. If you're a customer, you can open an incident with Teradata Customer Service to report the problem.