How to Resolve ORA-00924: missing BY keyword

ORA-00924

ORA-00924 means that the reserved word BY is missing from the statement at the position where it points out in the error message.

ORA-00924 could be seen in the following syntaxes.

1. GROUP BY
2. ORDER BY
3. IDENTIFIED BY
4. PARTITION BY
5. CONNECT BY

Let's see some error patterns.

GROUP BY (SELECT)

SQL> select job_id, count(*) num from employees group job_id;
select job_id, count(*) num from employees group job_id;
                                                 *
ERROR at line 1:
ORA-00924: missing BY keyword

To fix it, we should put the keyword BY right after GROUP.

SQL> select job_id, count(*) num from employees group by job_id;
...

ORDER BY (SELECT)

SQL> select job_id, count(*) num from employees group by job_id order 2 desc;
select job_id, count(*) num from employees group by job_id order 2 desc
                                                                 *
ERROR at line 1:
ORA-00924: missing BY keyword

To fix it, we should put the keyword BY right after ORDER.

SQL> select job_id, count(*) num from employees group by job_id order by 2 desc;
...

IDENTIFIED BY (ALTER USER, CREATE USER, CREATE DATABASE LINK)

SQL> alter user foo identified foo;
alter user foo identified foo
                          *
ERROR at line 1:
ORA-00924: missing BY keyword

For the same reason, we put the keyword BY right after IDENTIFIED.

SQL> alter user foo identified by foo;

User altered.

PARTITION BY (CREATE TABLE)

SQL> create table products (prod_id int) partition range (prod_id) (partition products_old values less than (10000));
create table products (prod_id int) partition range (prod_id) (partition products_old values less than (10000))
                                              *
ERROR at line 1:
ORA-00924: missing BY keyword

For the same reason, we put the keyword BY right after PARTITION.

SQL> create table products (prod_id int) partition by range (prod_id) (partition products_old values less than (10000));

Table created.

Problems fixed.