From keyword not found where expected oracle ошибка

The ORA-00923: FROM keyword not found where expected error occurs when the FROM keyword is missing, misspelled, or misplaced in the Oracle SQL statement such as select or delete. The FROM keyword is used to identify the table name. If an error occurs when checking for the FROM keyword, the table name cannot be found. If the the FROM keyword is missing from the SQL query, misspelled, or misplaced in the select statement, Oracle parser will fail to detect the FROM keyword. If the FROM keyword is not found where it should be, an error message ORA-00923: FROM keyword not found where expected will be displayed.

The FROM keyword is used in the select statement to specify the table or view name. The select statement could not identify the table names if the FROM keyword was missing, misspelled, or misplaced. The issue is caused by the FROM keyword or the code written before the FROM keyword. The issue ORA-00923: FROM keyword not found where expected will be fixed if you rewrite the select query along with the FROM keyword.

When this ORA-00923 error occurs

The error will occur in the Oracle database if the FROM keyword is missing, misspelled, or misplaced in the select statement. The error will occur if there are any errors in the code written before the FROM keyword. If you examine the sql query in and around the FROM keyword, you may be able to rectify the error.

select id,name emp;

ORA-00923: FROM keyword not found where expected
00923. 00000 -  "FROM keyword not found where expected"
*Cause:    
*Action:
Error at Line: 9 Column: 18

Root Cause

The FROM keyword will be used in the select statement to specify the table or view name from which the data will be retrieved. If the FROM keyword is missing, misplaced, or misspelled in the select statement, the table name cannot be identified. In this example, the select statement expects the presence of a FROM keyword in the query. As a result, an Oracle error “ORA-00923: FROM keyword not found where expected” will be thrown.

Solution 1

If the FROM keyword is not present in the select statement, it should be added before the table or view name. The select statement reads the table or view name and retrieves data from it. Check that the FROM keyword is present in the select query.

Problem

select id,name emp;

ORA-00923: FROM keyword not found where expected
00923. 00000 -  "FROM keyword not found where expected"

Solution

select id,name FROM emp;

Solution 2

Oracle will give an error if the FROM keyword in the select statement is misspelled. The FROM keyword was not found in the query, thus the select statement failed. In this scenario, the table or view name could not be discovered. The FROM keyword must be properly written. The FROM keyword is case insensitive.

Problem

select id,name FRM emp;

ORA-00923: FROM keyword not found where expected
00923. 00000 -  "FROM keyword not found where expected"

Solution

select id,name FROM emp;

Solution 3

If the FROM keyword is misplaced in the select statement, an error notice will be produced. The FROM keyword should come before the table or view name and after the list of column names. If the FROM keyword is used in combination with the name of a column, Oracle will consider the FROM keyword to be one of the columns and will expect the FROM keyword to identify the table or view name.

Problem

select id,name, FROM emp;

ORA-00923: FROM keyword not found where expected
00923. 00000 -  "FROM keyword not found where expected"

Solution

select id,name FROM emp;

Solution 4

If there is a mistake in the list of column names in the select statement, an error message will be displayed. Oracle will treat the error in the column list and expects the FROM keyword in the select statement to specify the table or view name. The column name contains a space in between and no double quotes is used to the column name.

Problem

select id, name as senior manager from emp;

ORA-00923: FROM keyword not found where expected
00923. 00000 -  "FROM keyword not found where expected"

Solution

select id, name as "senior manager" from emp;

Solution 5

The error will be thrown if the column name is identified with a single quotation. The column name should be included in double quotation marks. The issue may be fixed by changing the single quotation to a double quotation. In Oracle, a single quote will be used to identify the string value or date value. In the column names, a double quotation should be utilized.

Problem

select id, name as 'senior manager' from emp;

ORA-00923: FROM keyword not found where expected
00923. 00000 -  "FROM keyword not found where expected"

Solution

select id, name as "senior manager" from emp;

Solution 6

The from keyword error happens in the Oracle database if any of the oracle keywords is used as the column names in the select statement. The keyword should not be used as the name of a column. The issue may be fixed by deleting the keyword from the column name.

Problem

select id as size from emp;

ORA-00923: FROM keyword not found where expected
00923. 00000 -  "FROM keyword not found where expected"

Solution

select id as size_ from emp;

Hello everybody,

i try to get some information from my oracle database. I use the SQL code which is attached to get the informatio. Unfortunately, i always get this failure: Oracle database error 923: ORA-00923: FROM keyword not found where expected.

I have no idea what there is missing. Could anyone help me out in this issue? I really dont know where the failure is…. I printed all out but i didnt find it…

It would be awesome! Thank you in advance.

Best regards

Nicole

SELECT mkpf.budat, mkpf.FRBNR, mseg.CPUDT_MKPF, mkpf.mblnr, mkpf.SPE_BUDAT_UHR AS CPUTM_MKPF, mkpf.mjahr as kopfjahr, mkpf.cputm, mseg.matnr, mseg.mjahr as segjahr, mseg.zeile, mseg.mblnr,

  mseg.bwart, mseg.werks, mseg.erfmg, mseg.dmbtr, mseg.lgort

  ltap.wdatu ,ltap.qdatu, ltap.qzeit, ltap.VLPLA, ltap.NLPLA, ltap.VLTYP, ltap.NLTYP, ltap.VSOLA, ltap.NSOLA, ltap.werks, ltap.wenum, ltap.wepos, ltak.BDATU, ltak.BZEIT, ltap.TANUM, ltak.tanum, ltak.lgnum, ltak.mblnr

FROM (SELECT LOGSYS, MJAHR, MBLNR, FRBNR, BUDAT, CPUTM, SPE_BUDAT_UHR FROM S_TEC_LOGISTICS.V_MKPF

WHERE LOGSYS=’SAPP81011′ AND MJAHR=’2017′ AND FRBNR != ‘ ‘ AND BUDAT > 20170801) mkpf

INNER JOIN

(SELECT LOGSYS, WERKS, MATNR, MJAHR,

   MBLNR, ZEILE ,BWART,

   WERKS AS WERK, ERFMG, DMBTR, LGORT FROM S_TEC_LOGISTICS.V_MSEG

WHERE LOGSYS= ‘SAPP81011’ AND MJAHR = ‘2017’ AND WERKS = ‘9820’

      AND LGORT = ‘9810’ AND CPUDT_MKPF >= 20170801 AND BWART = ‘101’) mseg

ON mkpf.MBLNR = mseg.MBLNR AND mkpf.MJAHR = mseg.MJAHR

INNER JOIN (SELECT LOGSYS,  WERKS, QDATU, QZEIT, VLPLA, NLPLA, VLTYP, NLTYP, VSOLA, NSOLA, WENUM, WEPOS, LGNUM, TANUM FROM S_TEC_LOGISTICS.V_LTAP

  WHERE LOGSYS = ‘SAPP81011’ AND WERKS = ‘9820’ AND QDATU >= 20170801 AND LGNUM = ’98G’ AND VLTYP in (‘902′,’423’) AND NLTYP in (‘423′,’424′,’421’)) ltap

ON mseg.MBLNR = ltap.WENUM AND mseg.ZEILE = ltap.WEPOS

INNER JOIN (SELECT LOGSYS, TANUM, LGNUM, MBLNR, BDATU, BZEIT FROM S_TEC_LOGISTICS.V_LTAK WHERE LOGSYS = ‘SAPP81011′ AND LGNUM = ’98G’ AND SUBSTR(QDATU,1,4) = ‘2017’) ltak

    ON mkpf.LOGSYS = ltak.LOGSYS AND ltap.TANUM = ltak.TANUM AND ltap.LGNUM = ltak.LGNUM

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;

A | B | C
20 | 20 | 23
20 | 20 | 20
20 | 21 | 22
13 | 14 | 30

SELECT * AS 'Isosceles'
FROM TRIANGLES
WHERE TRIANGLES.A = TRIANGLES.B;

3 ответа

Лучший ответ

select object_id as 'a' from all_objects where 1=0;
ERROR at line 1:
ORA-00923: FROM keyword not found where expected

select object_id as a from all_objects where 1=0;
no rows selected (success)

select object_id as "a" from all_objects where 1=0;
no rows selected (success)

select triangles.*, 'Isosceles' triangle_type
from triangles
WHERE TRIANGLES.A = TRIANGLES.B;

SQL> with test (a, b, c) as
  2    (select 20, 20, 23 from dual union all
  3     select 20, 20, 20 from dual union all
  4     select 20, 21, 22 from dual union all
  5     select 13, 14, 30 from dual
  6    )
  7  select a, b, c,
  8         case when a = b then 'Isosceles'
  9         else 'not isosceles'
 10         end result
 11  from test;

         A          B          C RESULT
---------- ---------- ---------- -------------
        20         20         23 Isosceles
        20         20         20 Isosceles
        20         21         22 not isosceles
        13         14         30 not isosceles

SQL>

A   B   C   IS_ISOSCELES
20  20  23  Isosceles
20  20  20  Isosceles
20  21  22  not isosceles
13  14  30  not isosceles