Create Table AS

[Guest.Visitor]

Diane Mueller wrote: > create TABLE mylib.testp > as (select * > from mylib.orders > where custyear = '03') > > Syntax error - but I can't find it. Diane, Try using a slash instead of a dot to separate the library and file names. Bill

[Diane Mueller]

Create Table AS

Bill, Thanks for the thought, but it didn't help. Here is my error---any clue on what it is saying "with definition"?? Diane create TABLE mylib.testp as (select * from mylib.orders where sryear = '03') [SQL0104] Token was not valid. Valid tokens: WITH DEFINITION. Cause . . . . . : A syntax error was detected at token . Token is not a valid token. A partial list of valid tokens is WITH DEFINITION. This list assumes that the statement is correct up to the token. The error may be earlier in the statement, but the syntax of the statement appears to be valid up to this point. Recovery . . . : Do one or more of the following and try the request again: -- Verify the SQL statement in the area of the token . Correct the statement. The error could be a missing comma or quotation mark, it could be a misspelled word, or it could be related to the order of clauses. -- If the error token is , correct the SQL statement because it does not end with a valid clause.

[Guest.Visitor]

Create Table AS

I think it's a little more involved than that. Try this: Create VIEW LIB/MYVIEW AS SELECT * FROM MYFILE Create TABLE LIB/MYTABLE like LIB/MYVIEW DROP VIEW LIB/MYVIEW CASCADE Chris

[Diane Mueller]

Create Table AS

Chris, I see what you are doing here and I'm testing on this, on the second create TABLE, it did not bring my data into the table. MYVIEW is created with the data, but MYTABLE was created with no data. I would not need an insert statement, would I?? So, one cannot simply CREATE TABLE AS with a subquery defining the table as I tried to do above?? Diane

[DougCMH]

Create Table AS

What is it you are looking to do? Do you simply want a view of records for 2003, without actually having a copy of the data in another table? In that case, your statement is correct if you use CREATE VIEW. Really, your statement is a valid CREATE VIEW statement, not CREATE TABLE. Or do you want a duplicate table, with a copy of the data? In that case, try these SQL statements:

 create TABLE mylib.testp like mylib.orders; insert into mylib.testp (select * from mylib.orders where custyear = '03'); 

[Diane Mueller]

Create Table AS

Doug, Originally I wanted another table based on a selection criteria, now I'm rethinking this and maybe I only need a VIEW. (I'm working with Oracle examples and then trying to mimic them on the iSeries--some of the Oracle examples do not work exactly the same on the iSeries). Thanks for the help and examples from all! Diane

[Diane Mueller]

Create Table AS

I need to create a table from a subselect statement - but I continually am receiving errors with this statement - seems pretty simple......any suggestions would be appreciated! create TABLE mylib.testp as (select * from mylib.orders where custyear = '03') Syntax error - but I can't find it. Diane

[Guest.Visitor]

Create Table AS

create TABLE mylib.testp as (select * from mylib.orders where sryear = '03') with definition