RE: CREATE TABLE as sub-query with primary key syntax

I think you forgot to put the ending paren on the select clause....

CREATE TABLE TEMP AS (                        
  SELECT DBIFIL, DBIFMT, DBIFLD, DBIUPD          
    FROM QSYS/QADBIFLD                           
   WHERE DBIFIL = "TEST")  -- Added paren
  PRIMARY KEY(DBIFIL, DBIFMT, DBIFLD)            
  WITH DATA;


-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx]On Behalf Of Peter Dow (ML)
Sent: Monday, March 12, 2007 1:24 PM
To: Midrange Systems Technical Discussion
Subject: CREATE TABLE as sub-query with primary key syntax


I'm trying to create a table with this by putting the following into a 
source member and using RUNSQLSTM:

CREATE TABLE TEMP AS (                        
  SELECT DBIFIL, DBIFMT, DBIFLD, DBIUPD          
    FROM QSYS/QADBIFLD                           
   WHERE DBIFIL = "TEST"
  PRIMARY KEY(DBIFIL, DBIFMT, DBIFLD)            
  WITH DATA;

It doesn't like the PRIMARY KEY() clause.  I checked the v5r3 SQL 
manual, and it doesn't give any examples of doing it, but has the 
following to say under the "as sub-query" heading of the CREATE TABLE 
command:

"The implicit definition does not include any other optional attributes 
of the identified table or view. For example, the new table does not 
automatically include a primary key or foreign key from a table. The new 
table has these and other optional attributes only if the optional 
clauses are explicitly specified."

which to me implies that I can use the PRIMARY KEY clause, I just don't 
have the syntax right.

Does anyone know the correct syntax, or whether this is allowed or not?

*Peter Dow* /
Dow Software Services, Inc.
909 793-9050
pdow@xxxxxxxxxxxxxxx <mailto:pdow@xxxxxxxxxxxxxxx> /