ORA-00923 FROM Keyword Not Found Where Expected - Resolution Guide

ORA-00923: FROM Keyword Not Found Where Expected

Error Overview

Error Text: ORA-00923: FROM keyword not found where expected

This error occurs when Oracle’s SQL parser expects a FROM clause but finds something else instead. It’s frequently caused by incorrect column alias syntax, missing commas, or SQL syntax that’s valid in other databases but not in Oracle.

Common Causes

1. Using AS for Table Aliases

• Oracle does NOT support AS for table aliases (only for column aliases)
• Common mistake from MySQL/PostgreSQL/SQL Server users

2. Missing Commas in SELECT List

• Forgotten comma between column expressions
• Oracle interprets the second column as a table alias

3. Invalid Expression in SELECT

• Unsupported syntax or functions
• Missing operators between expressions

4. Reserved Word Used as Alias

• Using Oracle reserved words without quoting

5. Subquery Syntax Errors

• Malformed inline views
• Missing parentheses around subqueries

Diagnostic Examples

Example 1: AS Keyword for Table Alias (Most Common)

-- ERROR: Oracle doesn't support AS for table aliases
SELECT e.name, d.dept_name
FROM employees AS e
JOIN departments AS d ON e.dept_id = d.dept_id;
-- ORA-00923: FROM keyword not found where expected

-- FIX: Remove AS from table aliases
SELECT e.name, d.dept_name
FROM employees e
JOIN departments d ON e.dept_id = d.dept_id;

-- NOTE: AS is valid for COLUMN aliases
SELECT name AS employee_name, salary AS annual_salary
FROM employees;

Example 2: Missing Comma in SELECT List

-- ERROR: Missing comma between salary and department
SELECT employee_id, name, salary department
FROM employees;
-- This actually runs but treats 'department' as alias for 'salary'
-- The real error occurs when you add more columns:

SELECT employee_id, name salary, department, hire_date
FROM employees;
-- ORA-00923 because Oracle sees 'name salary' as column+alias,
-- then 'department' as another alias (can't have two aliases)

-- FIX: Add the missing comma
SELECT employee_id, name, salary, department, hire_date
FROM employees;

Example 3: Invalid String Concatenation

-- ERROR: Using + for string concatenation (SQL Server syntax)
SELECT first_name + ' ' + last_name
FROM employees;
-- ORA-00923: FROM keyword not found where expected

-- FIX: Use || for concatenation in Oracle
SELECT first_name || ' ' || last_name
FROM employees;

Example 4: TOP N Syntax (SQL Server)

-- ERROR: TOP is not Oracle syntax
SELECT TOP 10 * FROM employees;
-- ORA-00923: FROM keyword not found where expected

-- FIX: Use FETCH FIRST (Oracle 12c+)
SELECT * FROM employees
FETCH FIRST 10 ROWS ONLY;

-- Or use ROWNUM (all versions)
SELECT * FROM employees
WHERE ROWNUM <= 10;

Example 5: Square Brackets for Identifiers

-- ERROR: Square brackets are SQL Server syntax
SELECT [employee_id], [name] FROM [employees];
-- ORA-00923: FROM keyword not found where expected

-- FIX: Use double quotes for identifiers in Oracle
SELECT "employee_id", "name" FROM "employees";
-- Or better: don't quote unless case-sensitive names are needed
SELECT employee_id, name FROM employees;

Resolution Steps

Step 1: Check Table Alias Syntax

-- Remove all AS keywords from table aliases
-- WRONG                          -- RIGHT
-- FROM employees AS e            -- FROM employees e
-- JOIN departments AS d          -- JOIN departments d
-- LEFT JOIN locations AS l       -- LEFT JOIN locations l

Step 2: Verify Commas in SELECT List

-- Check every column has a comma separator
-- Tip: Put commas at the start of lines to spot missing ones easily
SELECT employee_id
     , first_name
     , last_name
     , salary
     , department_id
FROM employees;

Step 3: Check for Cross-Database Syntax

-- Common syntax differences:
-- SQL Server/MySQL        -- Oracle Equivalent
-- SELECT TOP 10           -- FETCH FIRST 10 ROWS ONLY
-- string1 + string2       -- string1 || string2
-- ISNULL(x, y)            -- NVL(x, y)
-- GETDATE()               -- SYSDATE
-- [column_name]           -- "column_name"
-- LIMIT 10                -- FETCH FIRST 10 ROWS ONLY
-- IF EXISTS               -- (not directly supported in SQL)

Step 4: Validate Reserved Words

-- Check if your alias is a reserved word
SELECT keyword FROM v$reserved_words
WHERE keyword = UPPER('your_alias')
  AND reserved = 'Y';

-- If you must use a reserved word as alias, quote it
SELECT count AS "COUNT", level AS "LEVEL"
FROM summary_table;

Common Cross-Database Migration Fixes

MySQL to Oracle

-- MySQL backticks -> Oracle double quotes (or remove)
-- MySQL: SELECT `order` FROM `table`
-- Oracle: SELECT "order" FROM "table"

-- MySQL LIMIT -> Oracle FETCH FIRST
-- MySQL: SELECT * FROM t LIMIT 10 OFFSET 20
-- Oracle: SELECT * FROM t OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY

SQL Server to Oracle

-- Remove all AS from table aliases
-- Replace + with || for strings
-- Replace TOP with FETCH FIRST
-- Replace square brackets with double quotes
-- Replace ISNULL with NVL
-- Replace GETDATE() with SYSDATE

Prevention Strategies

1. Use Oracle-Specific SQL Standards

• Never use AS for table aliases
• Use || for string concatenation
• Use FETCH FIRST instead of TOP/LIMIT
• Use NVL instead of ISNULL/IFNULL

2. SQL Linting

-- Test SQL in development before deploying
-- Oracle SQL Developer has built-in syntax validation
-- Consider using tools like SQLcl for command-line validation

3. Leading Comma Style

-- Makes missing commas immediately visible
SELECT e.employee_id
     , e.first_name
     , e.last_name
     , d.department_name
     , e.salary
FROM employees e
JOIN departments d ON e.department_id = d.department_id;

Related Errors

• ORA-00933: SQL Command Not Properly Ended - Trailing syntax not recognized
• ORA-00904: Invalid Identifier - Column or alias doesn’t exist
• ORA-00936: Missing Expression - Incomplete SQL expression
• ORA-00907: Missing Right Parenthesis - Unclosed parenthesis in expression