SQL Error on 'Create Table'

Database
Fan

SQL Error on 'Create Table'

Hi! Please help..I'm new to Teradata and am trying to run the following sql to create a table.

CREATE SET TABLE Address ,NO FALLBACK ,
NO BEFORE JOURNAL,
NO AFTER JOURNAL,
CHECKSUM = DEFAULT
(
AddressID INTEGER,
CustomerID INTEGER,
Address1 VARCHAR(128) CHARACTER SET LATIN NOT CASESPECIFIC,
Address2 VARCHAR(128) CHARACTER SET LATIN NOT CASESPECIFIC,
City VARCHAR(64) CHARACTER SET LATIN NOT CASESPECIFIC,
County VARCHAR(64) CHARACTER SET LATIN NOT CASESPECIFIC,
State CHAR(2) CHARACTER SET LATIN NOT CASESPECIFIC,
Zip VARCHAR(12) CHARACTER SET LATIN NOT CASESPECIFIC,
Zip5 DECIMAL(5,0),
Country VARCHAR(64) CHARACTER SET LATIN NOT CASESPECIFIC,
PrimaryAddressFlag CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC DEFAULT 'Y')
UNIQUE PRIMARY INDEX ( AddressID );

I keep getting the Syntax error : 3707, expected something like a 'LOG' keyword between ',', and the word 'CHECKSUM'.

What can I do to make it work?

11 REPLIES
Junior Contributor

Re: SQL Error on 'Create Table'

You're probably running the create on an older version of Teradata, CHECKSUM was implemented in V2R5.1 (?)

Just remove it and try it again...

Dieter
Fan

Re: SQL Error on 'Create Table'

Thanks! It worked :-)

Re: SQL Error on 'Create Table'

Hi,

I've got a similar problem with checksum:

CREATE SET TABLE BASE.SOME_TABLE ,NO FALLBACK ,
NO BEFORE JOURNAL,
NO AFTER JOURNAL,
CHECKSUM = DEFAULT
(
...
)
UNIQUE PRIMARY INDEX ( KEY );

"3706: Syntax error: Expected a CHECKSUM keyword"

Server: Teradata 06.02.0234 V2R
Sql Assistant: 7.2.0,06

My friends with identical sql assistant, parameters, and so on, can do this query, but I don't.

Any suggestions? Thanks in advance!
Enthusiast

Re: SQL Error on 'Create Table'

Checksum was included from V2R5.1 onwards. Your create looks perfectly fine and is working on Teradata 12.
Enthusiast

Re: SQL Error on 'Create Table'

Please excuse my newest... However I keep receiving this error when I try to create a table: 

create table failed 3707 syntax error, expected something like a 'CHECK' keyword or an 'UNIQUE' keyword between ')' and the string 'R' keyword

CREATE MULTISET TABLE base_06302012

(

WorkCaseId INT,

WorkCaseStageId INT,

Inserted INT,

InsertedBy VARCHAR(255),

LastModified DATE,

LastModifiedBy VARCHAR(255),

StartDate DATE,

EndDate DATE,

ChangeDate DATE,

AssignedTo VARCHAR(255),

AssignedBy VARCHAR(255),

AssignedOrganization VARCHAR(255),

AssignedLocation VARCHAR(255),

AssignedRole VARCHAR(255),

ProcessedDate DATE,

Comments VARCHAR(255),

WorkCaseInserted DATE,

WorkCaseInsertedBy VARCHAR(255),

WorkCaseStageStatus VARCHAR(255),

WorkCaseStageType VARCHAR(255),

WorkCaseName VARCHAR(255),

WorkCaseStatus VARCHAR(255),

IsActiveStage INT,

MinutesElapsed INT,

GeneratedDate DATE,

Identifier VARCHAR(255),

ClassificationCode VARCHAR(255),

TypeDate DATE,

SourceSystemIdentifier VARCHAR(255),

AccountNumber INT,

AlertSource VARCHAR(255),

SourceTerm VARCHAR(255),

CustomerName VARCHAR(255),

Application DATE,

Score INT,

AlertGeneratorId INT,

DataModelId INT,

REPOriginationChannel VARCHAR(255),

REPBorrowerLastName VARCHAR(255),

REPBorrowerFirstName VARCHAR(255)

'REPMICert' VARCHAR(255),

'REPMICovg' INT,

REPInvestorLoanNumber VARCHAR(255),

REPNextDueDate DATE,

REPLoanStatus VARCHAR(255),

REPPropertyStreetNumber VARCHAR(255),

REPPropertyStreet VARCHAR(255),

REPUnit VARCHAR(255),

REPPropertyCity VARCHAR(255),

REPPropertyState VARCHAR(255),

REPPropertyZip VARCHAR(255),

REPPropertyType VARCHAR(255),

REPAppraisedValue VARCHAR(255),

REPAppraisalDate DATE,

REPOrigLoanAmount VARCHAR(255),

REPCurrentUPB VARCHAR(255),

REPOriginalLTV VARCHAR(255),

REPLoanTerm VARCHAR(255),

REPLoanProductcode VARCHAR(255),

REPFundingDate DATE,

REP1stpmtdate DATE,

REPDocType VARCHAR(255),

REPLoanPurposeCode VARCHAR(255),

REPOccupancyCode VARCHAR(255),

REPLoanApplicationDate VARCHAR(255),

REPHTI VARCHAR(255),

REPDTI VARCHAR(255),

REPFICOScoreDecisioning VARCHAR(255),

REPUWName VARCHAR(255),

REPMICompany VARCHAR(255),

REPNoticeType VARCHAR(255),

REPNoteRate VARCHAR(255),

REPProductDescription VARCHAR(255),

REPTradeNameNumber VARCHAR(255),

REPSettlementDate DATE,

REPServiceronRecord VARCHAR(255),

REPInvestor VARCHAR(255),

REPDemander VARCHAR(255),

RESOriginationChannel VARCHAR(255),

RESBorrowerLastName VARCHAR(255),

RESBorrowerFirstName VARCHAR(255),

RESMICert VARCHAR(255),

RESMICovg VARCHAR(255),

RESInvestorLoannumber VARCHAR(255),

RESNextDueDate VARCHAR(255),

RESLoanStatus VARCHAR(255),

RESPropertyStreetNumber VARCHAR(255),

RESPropertyStreet VARCHAR(255),

RESUnit VARCHAR(255),

RESPropertyCity VARCHAR(255),

RESPropertyState VARCHAR(255),

RESPropertyZip VARCHAR(255),

RESPropertyType VARCHAR(255),

RESAppraisedValue VARCHAR(255),

RESAppraisalDate DATE,

RESOrigLoanAmount VARCHAR(255),

RESCurrentUPB VARCHAR(255),

RESOriginalLTV VARCHAR(255),

RESLoanTerm VARCHAR(255),

RESLoanProductcode VARCHAR(255),

RESFundingDate DATE,

RES1stpmtdate VARCHAR(255),

RESDocType VARCHAR(255),

RESLoanPurposeCode VARCHAR(255),

RESOccupancyCode VARCHAR(255),

RESLoanApplicationDate VARCHAR(255),

RESHTI VARCHAR(255),

RESDTI VARCHAR(255),

RESFICOScoreDecisioning VARCHAR(255),

RESUWName VARCHAR(255),

RESMICompany VARCHAR(255),

RESNoticeType VARCHAR(255),

RESNoteRate VARCHAR(255),

RESProductDescription VARCHAR(255),

RESTradeNameNumber VARCHAR(255),

RESTradeSettlementDate VARCHAR(255),

RESServiceronRecord VARCHAR(255),

RESInvestor VARCHAR(255),

RESDemander VARCHAR(255),

REPBondInsurer VARCHAR(255),

REPFilenetLink VARCHAR(255),

REPSubsequentDueDate VARCHAR(255),

REPComments VARCHAR(255),

REPBulk VARCHAR(255),

RESBondInsurer VARCHAR(255),

RESFilenetLink VARCHAR(255),

RESSubsequentDueDate VARCHAR(255),

RESComments VARCHAR(255),

RESBulk VARCHAR(255),

REPFollowUpDate VARCHAR(255),

REPLitigationAP VARCHAR(255),

REPLoanCloseDate VARCHAR(255),

REPCashOut VARCHAR(255),

REPSalePrice VARCHAR(255),

REPCombinedLTV VARCHAR(255),

REPServicerLoanNumber VARCHAR(255),

REPLienPosition VARCHAR(255),

RESFollowUpDate VARCHAR(255),

RESLitigationAP VARCHAR(255),

RESLoanCloseDate VARCHAR(255),

RESCashOut VARCHAR(255),

RESSalePrice VARCHAR(255),

RESombinedLTV VARCHAR(255),

RESServicerLoanNumber VARCHAR(255),

RESLienPosition VARCHAR(255)

)

PRIMARY INDEX (workcaseid);

Thanks in advance,

brimsuqt

Junior Contributor

Re: SQL Error on 'Create Table'

You need to dd a comma:

REPBorrowerFirstName VARCHAR(255)

and remove some quotes

'REPMICert' VARCHAR(255),

'REPMICovg' INT,

Dieter

Re: SQL Error on 'Create Table'

Hi, I have employee table and there are 100 records. there is empno column which is UPI and deptno column which in NUSI. Now when I am running the below query.

Select * from employee where deptno in(10,20) order by salary;

now when we execute this query, it will do All-AMP operation as NUSI is there in deptno column. it will hit the subtable and get all the rowhash for deptno 10 and 20. data might come from different AMP. Now question is while returning the recordset to user, where it will sort the data based on salary and how as I given "order by salary" in my select statement.

Thanks in advance.

-Ranjit

Enthusiast

Re: SQL Error on 'Create Table'

Hi! 

The "order by" isn't executed until the entire set has been generated.  

Sorting doesn't occur until the query execution has completed.  I'm not sure about your statement that the "..all-amp operation as NUSI", if the NUSI is being used you should see a reference to "Index#4" or a multiple of four if there are multiple NUSI's on a table.  If you don't see that in the explain plan, you can drop your NUSI and save some I/O overhead.

The data is "assembled" from all of the participating amps and then sorted.

hope that helps...

Teradata Employee

Re: SQL Error on 'Create Table'

I am trying the below-mentioned command:

 

CREATE TABLE svm_iris (id integer,
attribute varchar(40) NOT NULL,
value DOUBLE PRECISION,
label varchar(40)NOT NULL);

 

After query execution, error thrown by system is as following :

value DOUBLE PRECISION,
$
*** Failure 3707 Syntax error, expected something like a 'CHECK' keyword be
tween ',' and the 'value' keyword.
Statement# 1, Info =74