Error 5505 Column data types cannot be specified with a WITH [NO] DATA clause.

Database

Error 5505 Column data types cannot be specified with a WITH [NO] DATA clause.

I get this error when I create the second of following statements and do not know why.

Statement 1:

CREATE VOLATILE TABLE wow  (

id DEC(18,0)

) UNIQUE PRIMARY INDEX(item_id)

ON COMMIT PRESERVE ROWS;

Statement 2:

INSERT INTO wow  VALUES (?);

After statement 1 I switch to 'Import Data' in my SQL assistant and select the appropriate file (1 line per row). SQL assistant is version 14. I had version 13 previously and it worked with it perfectly, but not anymore with version 14. Any suggestions why?

6 REPLIES
Enthusiast

Re: Error 5505 Column data types cannot be specified with a WITH [NO] DATA clause.

Hi,

Are you using the column correctly? Please check it.

Cheers,

Raja

Enthusiast

Re: Error 5505 Column data types cannot be specified with a WITH [NO] DATA clause.

HI,

You are defining a column name as id, and in PI clause you are specifying item_id which do not exist. else the statement is working fine. 

you can try

CREATE VOLATILE TABLE wow  (
id DEC(18,0)
) UNIQUE PRIMARY INDEX(id)
ON COMMIT PRESERVE ROWS;
Khurram

Re: Error 5505 Column data types cannot be specified with a WITH [NO] DATA clause.

Hi Khurram,

Thanks for your answer. It was my fault that I copied and pasted it incorrectly. I have tried your suggestion of course before I wrote to this forum. So the creation of the table works well, but not the insert statement afterwards. The error still persists.

Best regards

P.S.: Thanks Raja as well.

Enthusiast

Re: Error 5505 Column data types cannot be specified with a WITH [NO] DATA clause.

I tested this scenario using same statements, and it is working fine for me.

Versions details:

SQL Assistant : 14.0.0.1
Database : 13.00.00.12
Provider : Teradata.Net 13.11.0.1

This is also working fine with ODBC driver.

Provider      : ODBC 14.00.00.02

Enthusiast

Re: Error 5505 Column data types cannot be specified with a WITH [NO] DATA clause.

Dear Kawish,

You are the man I have always idealized throughout my career, from Info-Architects to GCC, But I am unable to connect with you. Can I have your email or some other link? I am even unable to connect you on linked in.

Khurram
Enthusiast

Re: Error 5505 Column data types cannot be specified with a WITH [NO] DATA clause.

Thanks Khurram for the compliments. I sent you invite on linkedin, stay in touch there.