All Forums Database
achikan01 13 posts Joined 06/12
17 Dec 2014
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?

ulrich 816 posts Joined 09/09
18 Dec 2014

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...

feel free to donate bitcoin:12kgAUHFUqvG2sQgaRBXFhCwyf9HXdkGud

achikan01 13 posts Joined 06/12
18 Dec 2014

There is no concerns.
My question is why  the second & third column showing as reserved word?

tomnolan 594 posts Joined 01/08
18 Dec 2014

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.

You must sign in to leave a comment.