Re: Create table as ...

Wei Tang wrote:
>
> Hi, there:
>
> I wonder if anyone of you ever used "create table as subquery" statement
> to create a table.
>
> I have a table called test:
> create table test(
> name varchar2(10),
> ID number);
>
> But I cannot use the following statement:
> create table test_bak as select * from test order by ID;
>
> It said: ORA-00933: SQL command not properly ended.
>
> When I remove the "order by" clause, everything is fine. I couldn't find
> any documentation saying "order by" is not supported in "create table
> as..." statement.
>
> What's wrong with that?
>
> I need your expertise in a rush. Please help me.
>
> Thanks.

From the Oracle 7 Server SQL Reference Manual:

The ORDER BY clause is subject to the following restrictions:

     If the ORDER BY clause and the DISTINCT operator both appear in a SELECT statement, the ORDER BY clause cannot

     refer to columns that do not appear in the select list.

     The ORDER BY clause cannot appear in subqueries within other statements.

     The total number of bytes in all expressions in the ORDER BY clause is limited to the size of a data block minus some

     overhead. The size of a data block is specified by the initialization parameter DB_BLOCK_SIZE. Received on Fri Jan 16 1998 - 00:00:00 CST