Solve SQL Error ORA-00984: Column Not Allowed Here

This occurs when a column is used in an incorrect context in an SQL query. This error is common when a column name is mistakenly used as a value in an INSERT statement or a constant in a SELECT clause. Here are some possible reasons for this error and ways to solve it with example codes.

1. Using a column name instead of a value in an INSERT

Incorrect Code:

INSERT INTO employees (employee_id, first_name, last_name)
VALUES (employee_id, 'John', 'Doe');

Correct Code:

INSERT INTO employees (employee_id, first_name, last_name)
VALUES (1, 'John', 'Doe');

Replace the column name (employee_id) with a valid value (1) in the VALUES clause.

2. Using a column name as a constant in a SELECT

Incorrect Code:

SELECT employee_id, first_name, last_name, salary * 1.1 AS new_salary
FROM employees
WHERE salary > employee_id;

Correct Code:

SELECT employee_id, first_name, last_name, salary * 1.1 AS new_salary
FROM employees
WHERE salary > 1000;

Replace the column name (employee_id) with a valid constant value (1000) in the WHERE clause.

3. Using an invalid alias in a SELECT

Incorrect Code:

SELECT e.employee_id, d.department_name
FROM employees e, departments d
WHERE e.department_id = d.department_id
AND d.department_name = department_name;

Correct Code:

SELECT e.employee_id, d.department_name
FROM employees e, departments d
WHERE e.department_id = d.department_id
AND d.department_name = 'Sales';

Replace the column name (department_name) with a valid value (‘Sales’) in the WHERE clause.

4. Using column names in place of expressions in a GROUP BY

Incorrect Code:

SELECT department_id, COUNT(employee_id)
FROM employees
GROUP BY department_id HAVING COUNT(employee_id) > employee_id;

Correct Code:

SELECT department_id, COUNT(employee_id)
FROM employees
GROUP BY department_id HAVING COUNT(employee_id) > 5;

Replace the column name (employee_id) with a valid value (5) in the HAVING clause.

In summary, always double-check your SQL queries for proper use of column names and constants, and ensure you are using them in the correct context.

Leave a Comment

Your email address will not be published. Required fields are marked *

Scroll to Top