telling me i got a missing right parenthesis idk why

Question

create table salary_cp 
as 
( select  rownum ,first_name,job_id,salary  from employees   order by salary desc  );

You probably should check the correct statement syntax for the DBMS that you're using — mustaccio, Apr 02 '20 at 22:15
ROWNUM is an Oracle keyword and cannot be used as a column name. You need to give it an alias e.g. `rownum as id`. Note that ID will not sort in SALARY DESC order because ROWNUM is generated **before** Oracle sorts the result set. — APC, Apr 03 '20 at 06:15

score 2 · Answer 1 · edited Apr 02 '20 at 22:29

2

Remove order by statement and then try

 create table salary_cp as 
( select  *  from employees)

edited Apr 02 '20 at 22:29

DCR

14,737
12
52
115

answered Apr 02 '20 at 22:16

Himanshu

3,830
2
10
29

Whosoever has downvoted check this first https://stackoverflow.com/questions/985921/sql-error-with-order-by-in-subquery why order by doesnt work mainly in subquery and table statements – Himanshu Apr 02 '20 at 22:21

GMB · Answer 2 · 2020-04-02T22:36:31.763

0

I think that you want to create a table with an additional columns that ranks employees by salary.

This might work:

create table salary_cp as 
select rownum rn, first_name, job_id, salary from employees order by salary desc;

But note that this is simpler done with window functions, which removes the need for an outer sort:

create table salary_cp as 
select row_number() over(order by salary) rn, first_name, job_id, salary from employees;

edited Apr 02 '20 at 22:36

answered Apr 02 '20 at 22:30

GMB

216,147
25
84
135

This does not answer the question of "what right-parenthesis is missing". Also - "might" makes me sad. :-( – Bob Jarvis - Слава Україні Apr 02 '20 at 22:37
1

@BobJarvis-ReinstateMonica: I don't see a reason to use a subquery in the first place... My first solution is to remove the parentheses . – GMB Apr 02 '20 at 22:38
1

What gets me is...why does Oracle complain about a *subquery* in this instance, when a non-subquery WITH THE EXACT SAME TEXT is accepted. Maybe they've been cooped up at home for too long...and the walls are closing in...BUT NOT ME! NO! NOT ME!! I AM AS SANE AS EVER!!! (And my brain-care specialist says I can say that, on occasion, if I'm quiet and polite about it... :-) – Bob Jarvis - Слава Україні Apr 02 '20 at 22:40
@BobJarvis-ReinstateMonica: aha, yes it's weird... but attempting to use a subquery this way is weird too, in my opinion! – GMB Apr 02 '20 at 22:41

score 0 · Answer 3 · answered Apr 02 '20 at 22:32

Some say you can't use an ORDER BY in a CREATE TABLE...AS - that's not entirely correct. What you CAN'T do is use ORDER BY in a subquery in a CREATE TABLE. For example

create table table2  as
  ( SELECT COL1, COl2 FROM TABLE1 ORDER BY COL1 )

will fail - but

create table table3 as
  SELECT COL1, COl2 FROM TABLE1 ORDER BY COL1

will work Just Fine (tm).

So will

create table table4 as
  (SELECT COL1, COl2 FROM TABLE1)

and

create table table5 as
  SELECT COL1, COl2 FROM TABLE1

Note that you can, sometimes, use ORDER BY in a subquery. For example:

SELECT COL1, COL2
  FROM (SELECT *
          FROM TABLE1
          ORDER BY COL2)

is accepted (at least in 18c), as is

SELECT COL1, COL2
  FROM (SELECT *
          FROM TABLE1)
  ORDER BY COL2

db<>fiddle here

Thats just a work around though not standard. Also by default create table takes attributes of the selected table not the data so sorted data using order by already doesnt make sense either. Moreover if create table is enforced with data too then you can sort it after loading unordered data that would also take same time — Himanshu, Apr 02 '20 at 22:42

telling me i got a missing right parenthesis idk why

3 Answers3