Error (Ora-00923: from Keyword Not Found Where Expected)

Error (ORA-00923: FROM keyword not found where expected)

Identifiers need to be quoted with double quotes ("). Single quotes (') denote a character value (not a "name").

Therefor you need to use:

SUM(part_gold) as "Number of Gold Medals"

More details in the manual:

• Database Object Names and Qualifiers
• Text literals

Leetcode Oracle - ORA-00923: FROM keyword not found where expected

If you need to select only all the columns then * without the alias is fine. But if You need to give alias of the table wherever you want to select all the columns of the table using * and also another expression in SELECT clause.

with TEMP AS 
( SELECT
    COL1,
    COL2
FROM table )
, TEMP1 AS 
(SELECT
    T.*, -- alias here
    "hello"   AS COL3
FROM TEMP T
)
select * FROM TEMP1;

with temp as
(
    select d.Name as Department,
    e.Name as Employee,
    e.Salary as Salary
    from employee e
    join department d
    on e.DepartmentId = d.Id
)
select T.* -- alias here
, rank() over (partition by department order by salary desc) as rr
from temp T;

java.sql.SQLSyntaxErrorException: ORA-00923: FROM keyword not found where expected

When concatenating your strings, there is no space between different commands

YOUR VERSION

"SELECT" +
    "Q1.ACCOUNT_NAME AS accountName," +
    "Q2.GROUP_NAME AS groupName" +
    "FROM USERS_DEV Q1" +
    "JOIN USERS_GROUPS Q2 ON Q1.ACCOUNT_NAME = Q2.ACCOUNT_NAME" +
    "WHERE LOWER(Q1.ACCOUNT_NAME) = 'john.lenon'",

So your SQL is "glued" together and doesn't form a correct SQL (e.g. SELECTQ1.ACCOUNT_NAME ... AS groupNameFROM USERS_DEV Q1)

HOW IT SHOULD BE

"SELECT" +
    " Q1.ACCOUNT_NAME AS accountName," +
    " Q2.GROUP_NAME AS groupName" +
    " FROM USERS_DEV Q1" +
    " JOIN USERS_GROUPS Q2 ON Q1.ACCOUNT_NAME = Q2.ACCOUNT_NAME" +
    " WHERE LOWER(Q1.ACCOUNT_NAME) = 'john.lenon'",

You need to add spaces in the end of each SQL line (or in the beginning, ofcourse).

ORA-00923: FROM keyword not found where expected, parameters in AS clause

It would probably help if you described why you want to dynamically change column aliases. Personally, I don't see any valid cause to do that, but hey - if you do, no objections.

From my point of view, it is PL/SQL you need and dynamic SQL. Pass desired aliases to the procedure and return refcursor which reflects what you've just passed. Here's an example:

SQL> create or replace procedure p_test
  2    (alias_1 in varchar2, alias_2 in varchar2, par_rc out sys_refcursor)
  3  is
  4    l_str varchar2(1000);
  5  begin
  6    l_str := 'select ename as "' || dbms_assert.simple_sql_name(alias_1) ||'"'||
  7             ', job as "'        || dbms_assert.simple_sql_name(alias_2) ||'"'||
  8             ' from emp where deptno = 20';
  9    open par_rc for l_str;
 10  end;
 11  /

Testing:

SQL> var rc refcursor
SQL> exec p_test('liTTle', 'f00t', :rc);

PL/SQL procedure successfully completed.

SQL> print :rc

liTTle     f00t
---------- ---------
SMITH      CLERK
JONES      MANAGER
SCOTT      ANALYST
ADAMS      CLERK
FORD       ANALYST

SQL>
SQL> exec p_test('brian', 'antiquena', :rc);

PL/SQL procedure successfully completed.

SQL> print :rc

BRIAN      ANTIQUENA
---------- ---------
SMITH      CLERK
JONES      MANAGER
SCOTT      ANALYST
ADAMS      CLERK
FORD       ANALYST

SQL>

FROM keyword not found where expected while filtering data

The railroad diagram in the documentation:

... shows that you can either use * on its own, or <something>.* along with other columns or expressions. So you need to precede your * with the table name or an alias:

SELECT * FROM 
      (SELECT i.*, (SELECT COUNT(*) FROM invoices) AS numberOfRows 
    FROM invoices i ORDER BY Id DESC) WHERE rownum <= 1 

If you're on a recent version of Oracle you can do this much more simply with:

select i.*, count(*) over () as numberOfRows
from invoices i
order by id desc
fetch first row only

On older version you still need a subquery, but only one level:

select *
from (
  select i.*, count(*) over () as numberOfRows
  from invoices i
  order by id desc
)
where rownum = 1

db<>fiddle

---

Related Topics