Issue with 'create table as select from'

Trevor_Newman

Hi,

 

I am trying to run the follwoing but get [Vertica][VJDBC](4856) ERROR: Syntax error at or near "AS" :-

 

create table reporting_staging.temp_compSMBmembers2
("col1" integer, 
col2 varchar(20)
)
AS
select col1,col2
from table
where year(date) = '2011' and month(date) = '09' ;

 

It looks like according to the docs it should work?

 

Any ideas?

 

Thanks,

SruthiA

Hi Newman,

 

     The syntax of the create table statement should be corrected slightly.

 

1) We do not specify data types of the column names while creating a table from select query. it inherits data type from the columns of select query.  Please try executing the following query. 

 

create table reporting_staging.temp_compSMBmembers2
(col1, col2)
AS
select col1,col2
from table
where year(date) = '2011' and month(date) = '09' ;

 

for more information, please refer to the documentation http://my.vertica.com/docs/7.1.x/HTML/index.htm#Authoring/SQLReferenceManual/Statements/CREATETABLE.htm?Highlight=create table

 

-Regards,

 Sruthi