OraFAQ Forum: SQL & PL/SQL » Creating table using a select statement

Home » SQL & PL/SQL » SQL & PL/SQL » Creating table using a select statement

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Creating table using a select statement [message #19014]

Tue, 26 February 2002 01:54

Vidya Anand
Messages: 4
Registered: February 2002

Junior Member

Can we use Order By clause in a select statement while creating a new table
ie Is this statement legal? Create table Table1 as select * from Table2 order by Field1
where <Field1 is a field in Table1>

Is this based on server settings?
This query works on one server and does not work on another server.
I am not able to find the reason for this.

Report message to a moderator

Re: Creating table using a select statement [message #19015 is a reply to message #19014]

Tue, 26 February 2002 02:43

pratap kumar tripathy
Messages: 660
Registered: January 2002

Senior Member

your query is fine . can u tell what exactly the error is.

Report message to a moderator

Re: Creating table using a select statement [message #19026 is a reply to message #19014]

Tue, 26 February 2002 08:19

Todd Barry
Messages: 4819
Registered: August 2001

Senior Member

Are you sure that both servers are running the same Oracle version?

It sounds like one of the servers is possibly running an older version that does not support the 'order by' clause in CTAS (create table as select).

Report message to a moderator

Re: Creating table using a select statement [message #19037 is a reply to message #19015]

Tue, 26 February 2002 19:24

Vidya Anand
Messages: 4
Registered: February 2002

Junior Member

<can u tell what exactly the error is>
Error
"SQL Command not properly ended"

Report message to a moderator

Re: Creating table using a select statement [message #19048 is a reply to message #19015]

Wed, 27 February 2002 01:08

pratap kumar tripathy
Messages: 660
Registered: January 2002

Senior Member

hi,

i tried here with 7 and 8i and it is fine.i have not got 8. i have no idea why is it happening.

if u find out the answer, please let me know.

best of luck!!!

cheers
pratap

Report message to a moderator