title: How would I create a table in Oracle 11g R2 from a SELECT AS and partition the table by range-list?
body: <p>I am trying to create a new table called Titles2 from an existing table called Titles. I have to use a SELECT AS statement to create the columns in Titles2 from Titles. I also have to partition Titles2 by RANGE and then by LIST.</p>
  
  <p>The code used to create Titles:</p>
  
  <pre><code>SQL&gt; CREATE TABLE Titles
    2    (
    3    Title_id         char(3)     ,
    4    Title            varchar2(40),
    5    Genre            varchar2(10),
    6    Pages            number      ,
    7    Price            number(5,2) ,
    8    Sales            number      ,
    9    Pub_id           char(3)     ,
   10    Pubdate          date        ,
   11    Advance          number(9,2) ,
   12    Royalty_rate     number(5,2) ,
   13    CONSTRAINT Titles_pk PRIMARY KEY (title_id),
   14    CONSTRAINT Titles_Publishers_fk FOREIGN KEY (Pub_id)
   15                             REFERENCES Publishers (pub_id)
   16    )
   17    PARTITION BY RANGE (Pubdate) (
   18    PARTITION P1 VALUES LESS THAN (TO_DATE('01-JAN-1995', 'DD-MON-YYYY')) TABLESPACE TSLab8ben1,
   19    PARTITION P2 VALUES LESS THAN (TO_DATE('01-JAN-2000', 'DD-MON-YYYY')) TABLESPACE TSLab8ben2,
   20    PARTITION P3 VALUES LESS THAN (MAXVALUE) TABLESPACE TSLab8ben3
   21    );
  
  Table created.
  </code></pre>
  
  <p>The following code is what I have so far for creating a Titles2 table from Titles:</p>
  
  <pre><code>CREATE TABLE Titles2 AS
  SELECT Title_id AS TID, Title, Genre, Sales, (Sales * Price) AS Revenue, Pub_id AS P#
  FROM Titles
  PARTITION BY RANGE (Revenue)
  SUBPARTITION BY LIST (Genre)
  SUBPARTITION TEMPLATE (
  SUBPARTITION G1 VALUES ('history', 'biography'),
  SUBPARTITION G2 VALUES ('computer','children'),
  SUBPARTITION G3 VALUES (DEFAULT)) (
  PARTITION P1 VALUES LESS THAN (100000),
  PARTITION P2 VALUES LESS THAN (500000),
  PARTITION P3 VALUES LESS THAN (1000000),
  PARTITION P4 VALUES LESS THAN (MAXVALUE));
  </code></pre>
  
  <p>However, the code for creating Titles2 doesn't execute. Would anybody be able to help me get the Titles2 code to execute? Thanks!</p>