Error in using a Create DDL in TD 12

Database
Fan

Error in using a Create DDL in TD 12

I am new to teradata and trying to run the sample fast load utility from the examples in the TD manuals,but when i took the DDL from the example and tried to create the table using TERADATA SQL assistant , it is throwing an error

RDBMS error:3707:Syntax error, expected something like a string or a Unicode character literal between the FORMAT keyword and the end of the request

The DDL i used is:

CREATE TABLE employee (

EmpNo SMALLINT FORMAT ‘9(5)’ BETWEEN 10001 AND 32001 NOT NULL,

Name VARCHAR(12),

DeptNo SMALLINT FORMAT ‘999’ BETWEEN 100 AND 900 ,

PhoneNo SMALLINT FORMAT ‘9999’ BETWEEN 1000 AND 9999,

JobTitle VARCHAR(12),

Salary DECIMAL(8,2) FORMAT ‘ZZZ,ZZ9.99’ BETWEEN 1.00 AND 999000.00 ,

YrsExp BYTEINT FORMAT ‘Z9’ BETWEEN -99 AND 99 ,

DOB DATE FORMAT ‘MMMbDDbYYYY’,

Sex CHAR(1) UPPERCASE,

Race CHAR(1) UPPERCASE,

MStat CHAR(1) UPPERCASE,

EdLev BYTEINT FORMAT ‘Z9’ BETWEEN 0 AND 22,

HCap BYTEINT FORMAT ‘Z9’ BETWEEN -99 AND 99 )

UNIQUE PRIMARY INDEX( EmpNo ) ;

I am not able to figure out what the issue is 

Can anyone help me out ??

Thanks

Viks

3 REPLIES
Supporter

Re: Error in using a Create DDL in TD 12

The quotes seem to be wrong

FORMAT ‘9(5)’ need to be

FORMAT '9(5)'

etc.

Fan

Re: Error in using a Create DDL in TD 12

It must have been the copy paste from the pdf which made it look weird

Thank you ulrich

Fan

Re: Error in using a Create DDL in TD 12

Hi,

this is working, as ulrich said, the problem was in quotes , replace ` by '  ,

thats it

CREATE  TABLE employee

(

        EmpNo SMALLINT FORMAT '9(5)' BETWEEN 10001 AND 32001 NOT NULL,

        Name VARCHAR(12),

        DeptNo SMALLINT FORMAT '999' BETWEEN 100 AND 900 ,

        PhoneNo SMALLINT FORMAT '9999' BETWEEN 1000 AND 9999,

        JobTitle VARCHAR(12),

        Salary DECIMAL(8,2) FORMAT 'ZZZ,ZZ9.99' BETWEEN 1.00 AND 999000.00 ,

        YrsExp BYTEINT FORMAT 'Z9' BETWEEN -99 AND 99 ,

        DOB DATE FORMAT 'MMMbDDbYYYY',

        Sex CHAR(1) UPPERCASE,

        Race CHAR(1) UPPERCASE,

        MStat CHAR(1) UPPERCASE,

        EdLev BYTEINT FORMAT 'Z9' BETWEEN 0 AND 22,

        HCap BYTEINT FORMAT 'Z9' BETWEEN -99 AND 99

        )

UNIQUE PRIMARY INDEX( EmpNo ) ;