ORA-00923
ORA-00923 is a commonly seen error that is easily resolved by simply correcting its syntax. Keep in mind ORA-00923 does not occur in Oracle 10g.
The Problem
When you are faced with this error, you will see the following message:
ORA-00923 FROM keyword not found where expected
ORA-00923 occurs when you try to execute a SELECT or REVOKE statement without a FROM keyword in its correct form and place. If you are seeing this error, the keyword FROM is spelled incorrectly, misplaced, or altogether missing. In Oracle, the keyword FROM must follow the last selected item in a SELECT statement or in the case of a REVOKE statement, the privileges. If the FROM keyword is missing or otherwise incorrect, you will see ORA-00923.
The Solution
To resolve ORA-00923, the user should make sure three possible causes are corrected. First, the user must correct the syntax. Make sure you have placed the keyword FROM in its correct place, and that no spelling errors have occurred. Secondly, if you used quotation marks in an alias, make sure that they have properly enclosed the alias and that they are double quotation marks. Lastly, make sure no reserved words were used as an alias. See the Oracle appendix for reserved words to view a complete list. For practical application of these practices on how to resolve ORA-00923, see the following examples.
In the following example, the query is missing the keyword FROM:
SELECT *
employees;
To correct the statement, insert the FROM keyword in the correct place, and run again:
SELECT *
FROM employees;
Another example of the ORA-00923 error is when quotation marks do not properly enclose the alias, as in the following:
SELECT manager AS manager column
FROM all_tables;
The alias—in this example, manager column—is not enclosed in double quotation marks. Resolve ORA-00923 by fixing this syntax mistake.
SELECT manager AS “manager column”
FROM all_tables;
Looking Forward
Avoiding ORA-00923 in the future is a matter of keeping to the proper syntax when executing SELECT or REVOKE statements. While correcting this error is not difficult, simply remember the following rules to avoid seeing this error.
Remember:
- The FROM keyword should follow the last selected item. Make sure it is not misspelled, misplaced, or missing.
- Make sure you have enclosed the alias in double quotation marks.
- Make sure no Oracle reserved word was used as an alias.
If you continue to experience this error, you may consider contacting your database administrator or a licensed Oracle consultant. Always check your consultant’s credentials and experience to ensure they meet your needs.
Learn the cause and how to resolve the ORA-00923 error message in Oracle.
Description
When you encounter an ORA-00923 error, the following error message will appear:
- ORA-00923: FROM keyword not found where expected
Cause
You tried to execute a SELECT statement, and you either missed or misplaced the FROM keyword.
Resolution
The option(s) to resolve this Oracle error are:
Option #1
This error can occur when executing a SELECT statement that is missing the FROM keyword.
For example, if you tried to execute the following SELECT statement:
SELECT * suppliers;
You could correct this SELECT statement by including the FROM keyword as follows:
SELECT * FROM suppliers;
Option #2
This error can also occur if you use an alias, but do not include the alias in double quotation marks.
For example, if you tried to execute the following SQL statement:
SELECT owner AS 'owner column' FROM all_tables;
You could correct this SELECT statement by using double quotation marks around the alias:
SELECT owner AS "owner column" FROM all_tables;
Option #3
This error can also occur if you add a calculated column to a SELECT * statement.
For example, if you tried to execute the following SQL statement:
SELECT *, CAST((FROM_TZ(CAST(last_modified_date AS timestamp),'+00:00') at time zone 'US/Pacific') AS date) AS "Local Time" FROM suppliers;
You could correct this SELECT statement by including the table name qualifier in front of the wildcard:
SELECT suppliers.*, CAST((FROM_TZ(CAST(last_modified_date AS timestamp),'+00:00') at time zone 'US/Pacific') AS date) AS "Local Time" FROM suppliers;
Option #4
You can also generate this error by having an unbalanced set of parenthesis.
For example, if you tried to execute the following SQL statement:
SELECT COUNT(*)) AS "Total" FROM suppliers;
You could correct this SELECT statement by removing the extra closing parenthesis just prior to the alias:
SELECT COUNT(*) AS "Total" FROM suppliers;
I tried to compile the below procedure in ORACLE DATABASE but getting Error
-PL/SQL: ORA-00923: FROM keyword not found where expected-
Procedure —
CREATE OR REPLACE PROCEDURE PendingTrailCasePropCrimeRepNw(
lang IN NUMBER,
date_from IN DATE,
date_to IN DATE,
cur OUT SYS_REFCURSOR)
IS
BEGIN
OPEN cur FOR SELECT
CASE
WHEN LANG=99 THEN
'MURDER FOR GAIN'
ELSE
'लाठके लिठहतà¥ÂÂया'
END
AS
'MURDER FOR GAIN',
---- ERROR STARTS HERE---
(SELECT COUNT(*)
FROM t_crime_major_minor_heads cdmh
INNER JOIN t_fir_registration fr
ON fr.FIR_REG_NUM=cdmh.FIR_REG_NUM
AND fr.LANG_CD =cdmh.LANG_CD
INNER JOIN t_final_report fnr
ON fnr.FIR_REG_NUM =cdmh.FIR_REG_NUM
AND fnr.LANG_CD =cdmh.LANG_CD
WHERE cdmh.MINOR_HEAD_CD =324
AND cdmh.MAJOR_HEAD_CD =36
AND fnr.FIR_REG_NUM NOT IN
(SELECT FIR_REG_NUM FROM t_court_disposal
)
AND fr.REG_DT BETWEEN TO_DATE(@startdate) AND TO_DATE(@enddate)
AND cdmh.LANG_CD=@LangCd
AND fr.PS_CD =@PS_CD
)
AS
PENDING_BEGINNING_YEAR,
(SELECT COUNT(*)
FROM t_crime_major_minor_heads cdmh
INNER JOIN t_fir_registration fr
ON fr.FIR_REG_NUM=cdmh.FIR_REG_NUM
AND fr.LANG_CD =cdmh.LANG_CD
INNER JOIN t_final_report fnr
ON fnr.FIR_REG_NUM =cdmh.FIR_REG_NUM
AND fnr.LANG_CD =cdmh.LANG_CD
WHERE cdmh.MINOR_HEAD_CD=324
AND cdmh.MAJOR_HEAD_CD =36
AND fr.REG_DT BETWEEN TO_DATE(@startdate) AND TO_DATE(@enddate)
AND cdmh.LANG_CD=@LangCd
AND fr.PS_CD =@PS_CD
)
AS
CASES_SEND_FOR_TRIAL,
(SELECT COUNT(*)
FROM t_crime_major_minor_heads cdmh
INNER JOIN t_fir_registration fr
ON fr.FIR_REG_NUM=cdmh.FIR_REG_NUM
AND fr.LANG_CD =cdmh.LANG_CD
INNER JOIN t_court_disposal cd
ON cd.FIR_REG_NUM =cdmh.FIR_REG_NUM
AND cd.LANG_CD =cdmh.LANG_CD
WHERE cdmh.MINOR_HEAD_CD=324
AND cdmh.MAJOR_HEAD_CD =36
AND fr.REG_DT BETWEEN TO_DATE(@startdate) AND TO_DATE(@enddate)
AND cdmh.LANG_CD=@LangCd
AND fr.PS_CD =@PS_CD
)
AS
CASES_DISPOSED,
(SELECT COUNT(*)
FROM t_crime_major_minor_heads cdmh
INNER JOIN t_fir_registration fr
ON fr.FIR_REG_NUM = cdmh.FIR_REG_NUM
AND fr.LANG_CD =cdmh.LANG_CD
WHERE MINOR_HEAD_CD =324
AND MAJOR_HEAD_CD =36
AND fr.FIR_REG_NUM NOT IN
(SELECT FIR_REG_NUM FROM t_final_report
)
AND fr.REG_DT > DATE_ADD(@startdate,INTERVAL '10' YEAR)
AND cdmh.LANG_CD=@LangCd
AND fr.PS_CD =@PS_CD
)
AS
MORE_THAN_10_YEARS,
(SELECT COUNT(*)
FROM t_crime_major_minor_heads cdmh
INNER JOIN t_fir_registration fr
ON fr.FIR_REG_NUM = cdmh.FIR_REG_NUM
AND fr.LANG_CD =cdmh.LANG_CD
WHERE cdmh.MINOR_HEAD_CD=324
AND cdmh.MAJOR_HEAD_CD =36
AND fr.FIR_REG_NUM NOT IN
(SELECT FIR_REG_NUM FROM t_final_report
)
AND fr.REG_DT BETWEEN DATE_ADD(@startdate,INTERVAL '5' YEAR) AND DATE_ADD(@enddate,INTERVAL '10' YEAR)
AND cdmh.LANG_CD=@LangCd
AND fr.PS_CD =@PS_CD
)
AS
MORE_THAN_5_LESS_THAN_10_YEARS,
(SELECT COUNT(*)
FROM t_crime_major_minor_heads cdmh
INNER JOIN t_fir_registration fr
ON fr.FIR_REG_NUM = cdmh.FIR_REG_NUM
AND fr.LANG_CD =cdmh.LANG_CD
WHERE cdmh.MINOR_HEAD_CD=324
AND cdmh.MAJOR_HEAD_CD =36
AND fr.FIR_REG_NUM NOT IN
(SELECT FIR_REG_NUM FROM t_final_report
)
AND fr.REG_DT BETWEEN DATE_ADD(@startdate,INTERVAL '4' YEAR) AND DATE_ADD(@enddate,INTERVAL '5' YEAR)
AND cdmh.LANG_CD=@LangCd
AND fr.PS_CD =@PS_CD
)
AS
MORE_THAN_4_LESS_THAN_5_YEARS,
(SELECT COUNT(*)
FROM t_crime_major_minor_heads cdmh
INNER JOIN t_fir_registration fr
ON fr.FIR_REG_NUM = cdmh.FIR_REG_NUM
AND fr.LANG_CD =cdmh.LANG_CD
WHERE cdmh.MINOR_HEAD_CD=324
AND cdmh.MAJOR_HEAD_CD =36
AND fr.FIR_REG_NUM NOT IN
(SELECT FIR_REG_NUM FROM t_final_report
)
AND fr.REG_DT BETWEEN DATE_ADD(@startdate,INTERVAL '3' YEAR) AND DATE_ADD(@enddate,INTERVAL '4' YEAR)
AND cdmh.LANG_CD=@LangCd
AND fr.PS_CD =@PS_CD
)
AS
MORE_THAN_3_LESS_THAN_4_YEARS,
(SELECT COUNT(*)
FROM t_crime_major_minor_heads cdmh
INNER JOIN t_fir_registration fr
ON fr.FIR_REG_NUM = cdmh.FIR_REG_NUM
AND fr.LANG_CD =cdmh.LANG_CD
WHERE cdmh.MINOR_HEAD_CD=324
AND cdmh.MAJOR_HEAD_CD =36
AND fr.FIR_REG_NUM NOT IN
(SELECT FIR_REG_NUM FROM t_final_report
)
AND fr.REG_DT BETWEEN DATE_ADD(@startdate,INTERVAL '2' YEAR) AND DATE_ADD(@enddate,INTERVAL '3' YEAR)
AND cdmh.LANG_CD=@LangCd
AND fr.PS_CD =@PS_CD
)
AS
MORE_THAN_2_LESS_THAN_3_YEARS,
(SELECT COUNT(*)
FROM t_crime_major_minor_heads cdmh
INNER JOIN t_fir_registration fr
ON fr.FIR_REG_NUM = cdmh.FIR_REG_NUM
AND fr.LANG_CD =cdmh.LANG_CD
WHERE cdmh.MINOR_HEAD_CD=324
AND cdmh.MAJOR_HEAD_CD =36
AND fr.FIR_REG_NUM NOT IN
(SELECT FIR_REG_NUM FROM t_final_report
)
AND fr.REG_DT BETWEEN DATE_ADD(@startdate,INTERVAL '1' YEAR) AND DATE_ADD(@enddate,INTERVAL '2' YEAR)
AND cdmh.LANG_CD=@LangCd
AND fr.PS_CD =@PS_CD
)
AS
MORE_THAN_1_LESS_THAN_2_YEARS,
(SELECT COUNT(*)
FROM t_crime_major_minor_heads cdmh
INNER JOIN t_fir_registration fr
ON fr.FIR_REG_NUM = cdmh.FIR_REG_NUM
AND fr.LANG_CD =cdmh.LANG_CD
WHERE cdmh.MINOR_HEAD_CD=324
AND cdmh.MAJOR_HEAD_CD =36
AND fr.FIR_REG_NUM NOT IN
(SELECT FIR_REG_NUM FROM t_final_report
)
AND fr.REG_DT BETWEEN DATE_ADD(@startdate,INTERVAL '6' MONTH) AND DATE_ADD(@enddate,INTERVAL '1' YEAR)
AND cdmh.LANG_CD=@LangCd
AND fr.PS_CD =@PS_CD
)
AS
MORE_THAN_6_MONTHS_LESS_THAN_1_YEAR,
(SELECT COUNT(*)
FROM t_crime_major_minor_heads cdmh
INNER JOIN t_fir_registration fr
ON fr.FIR_REG_NUM = cdmh.FIR_REG_NUM
AND fr.LANG_CD =cdmh.LANG_CD
WHERE cdmh.MINOR_HEAD_CD=324
AND cdmh.MAJOR_HEAD_CD =36
AND fr.FIR_REG_NUM NOT IN
(SELECT FIR_REG_NUM FROM t_final_report
)
AND fr.REG_DT < DATE_ADD(@startdate,INTERVAL '6' MONTH)
AND cdmh.LANG_CD=@LangCd
AND fr.PS_CD =@PS_CD
)
AS
LESS_THAN_6_MONTHS FROM dual;
END;
I am new with oracle i don’t know what is wrong in above procedure and how can resolve this.
Please help me to compile this. Thanks in advance
ERRORS —
PL/SQL: SQL Statement ignored
ORA-00923: FROM keyword not found where expected
ORA-00923
ORA-00923: ключевое слово FROM не было найдено, где оно ожидалось
Причина:
В операторах SELECT или REVOKE ключевое слово FROM скорее всего пропущено, неправильно размещено, или неправильно написано. Ключевое слово FROM должно сопровождаться последним элементом в SELECT операторе, или привилегией в REVOKE операторе.
Действие:
Вставьте ключевое слово FROM где это следует. Выбранный вами список сам по себе может быть ошибочным.
You need an alias for both derived tables. And the outer pair around the from clause is useless.
SELECT DISTINCT t1.unique_id AS uid, t1.confidence_is_same
FROM ( --<< only one opening parenthesis
SELECT unique_id,
confidence_is_same,
first_name,
last_name,
postal_code
FROM daniel.unique_physician
WHERE daniel.unique_physician.first_name = ''
AND daniel.unique_physician.last_name = ''
AND daniel.unique_physician.is_root_phys = 0
AND daniel.unique_physician.postal_code = ''
) t1 ---<< the alias for the derived table is missing
INNER JOIN (
SELECT max(confidence_is_same) OVER (PARTITION BY root_id) max_conf
FROM daniel.unique_physician
WHERE daniel.unique_physician.first_name = ''
AND daniel.unique_physician.last_name = ''
AND daniel.unique_physician.is_root_phys = 0
AND daniel.unique_physician.postal_code = ''
) t2 ON t1.confidence_is_same = t2.max_conf
But the join isn’t needed in the first place. Your query can be simplified to:
SELECT DISTINCT t1.unique_id AS uid, t1.confidence_is_same
FROM (
SELECT unique_id,
confidence_is_same,
max(confidence_is_same) OVER (PARTITION BY root_id) max_conf
FROM daniel.unique_physician unq
WHERE unq.first_name = ''
AND unq.last_name = ''
AND unq.is_root_phys = 0
AND unq.postal_code = ''
) t1
where confidence_is_same = max_conf;
You don’t need to select first_name, last_name and postal_code in the inner select as you don’t use them in the outer select. This can make the query potentially more efficient.
Additionally, the condition unq.last_name = '' won’t do what you think it does. Oracle does not have an «empty string». A string with length zero ('') will be stored as NULL, so what you really want is probably:
SELECT DISTINCT t1.unique_id AS uid, t1.confidence_is_same
FROM (
SELECT unique_id,
confidence_is_same,
max(confidence_is_same) OVER (PARTITION BY root_id) max_conf
FROM daniel.unique_physician unq
WHERE unq.first_name is null
AND unq.last_name is null
AND unq.is_root_phys = 0
AND unq.postal_code is null
) t1
where confidence_is_same = max_conf;