ORA-00924: missing BY keyword

ORA-00924: missing BY keyword error occurs when the BY keyword is missing in the ORDER BY, GROUP BY, CONNECT BY, IDENTIFIED BY and PARTITION BY clauses in the Oracle SQL query. The list of column names will be prefixed with the BY keyword. The BY keyword identifies column name based activities like as ordering, grouping, connecting, identifying, and partitioning. The column name based operation could not be done if the BY keyword was not present in the SQL query. The error message ORA-00924: missing BY keyword will be shown if the BY keyword is missing in the Oracle SQL query.

ORDER BY, GROUP BY, CONNECT BY, IDENTIFIED BY, and PARTITION BY clauses are used to perform a certain action depending on some column names given following the BY keyword. After querying the table’s data, the action will be executed in the table data cache. The BY keyword will not exist on its own. It is always used in conjunction with other keywords like order, group, connect, identify, and partition. If the keyword BY is missing with these keywords, the error ORA-00924: missing BY keyword will be thrown.

When the ORA-00924 error occur

If the clauses ORDER BY, GROUP BY, CONNECT BY, IDENTIFIED BY, and PARTITION BY are used in the sql query and the BY keyword is missing before the list of column names, an error message will be shown. The preceding phrase and the BY keyword should be used in tandem. If any of these is missing, the appropriate error will be shown.

Program

select * from employee order id;

error

ORA-00924: missing BY keyword
00924. 00000 -  "missing BY keyword"
*Cause:    
*Action:

Root cause

In clauses like ORDER BY, GROUP BY, CONNECT BY, IDENTIFIED BY, and PARTITION BY, the list of column names is prefixed with the BY keyword. Oracle could not find a list of column names to perform those clause operations if the BY keyword was absent. Along with these clause keywords, the BY keyword is always included. If the BY keyword is absent in these clauses, Oracle will throw an error.

Solution 1

The error will be thrown if the BY keyword is not included in the Oracle order by clause. The order by clause is used to organize rows according to a column. It might be done in either ascending or descending order. The sql query will get data from the tables, and the order by clause will sort the data in ascending or descending order based on the column name given in the clause.

Program

select * from employee order id;

ORA-00924: missing BY keyword
00924. 00000 -  "missing BY keyword"

Solution

select * from employee order by id;

Solution 2

The group by clause groups table data depending on the column name given in the group by clause. The group by clause aggregates the table data based on the column name supplied in the clause; the aggregated data is displayed in the sql query alongside the column names specified in the group by clauses.

Program

select * from employee group id;

ORA-00924: missing BY keyword
00924. 00000 -  "missing BY keyword"

Solution

select * from employee group by id;

Solution 3

In the sql query, the connect by keyword is used to search the data recursively. The connect by will be done in the data that has been saved as a parent-child-grand child pattern. The rows in the table will be reliant on one another. The connect by keyword will help in working with hierarchical data in Oracle tables.

Program

SELECT id, name, managerid, LEVEL FROM employee where LEVEL=2 
CONNECT PRIOR id = managerid;

ORA-00924: missing BY keyword
00924. 00000 -  "missing BY keyword"

Solution

SELECT id, name, managerid, LEVEL FROM employee where LEVEL=2 CONNECT BY PRIOR id = managerid;

Solution. 4

The identified by keyword is used in grand commands. It is used to give permissions for the users identified by clause. The database user name is added in the identified by clause to give the permission.

Program

alter user yawin identified tutor;

ORA-00924: missing BY keyword
00924. 00000 -  "missing BY keyword"

Solution

alter user yawin identified by tutor;

Solution 5

The sql query can summarise the data by logical or physical partitioning of the data in the table. The partition clause uses BY keyword along with PARTITION keyword.

Program

SELECT name, salary, deptid sum(salary) over (partition  deptid) total_salary from employee;

ORA-00924: missing BY keyword
00924. 00000 -  "missing BY keyword"

Solution

SELECT name, salary, deptid sum(salary) over (partition by deptid) total_salary from employee;