Getting "ORA-00923: FROM keyword not found where expected" in a Sybase-working query

Question

I have built the following query running fine on Sybase engines :

select *, 
(select COUNT(*) from myTable where column1 != '' and column2!=column3) as myField1,
(select COUNT(*) from myTable where column1 != '' and column2=column3) as myField2
from 
(select column1,column2,column3,column4,column5 
from myTable 
where column1 != '' and column2 != column3) as SUB_VIRTUAL_TABLE

However, running the same query on Oracle engines will raise the following exception:

ORA-00923: FROM keyword not found where expected

Could please anyone help to fix this syntax? Thanks a lot. P.s.: tables and columns exist, the query is indeed running fine when the engine is Sybase.

The expected result would look like this:

column1 | column2 | column3 | column4 | column5 | myField 1 | myField2
-----------------------------------------------------------------------
A         B         C         D         E         12          15 
E         F         G         H         I         12          15          
...

Unrelated but: `column1 != ''` will not do what you think it will. In Oracle there is no such thing as an empty string. You need to change that to `column1 is not null` — , Aug 03 '15 at 09:12

score 2 · Accepted Answer · edited May 23 '17 at 12:14

You don't use the as keyword in Oracle to alias subqueries. You just specify the alias. And you probably need to alias the * in your select

select sub_virtual_table.*, 
       (select COUNT(*) 
          from myTable 
         where column1 != '' 
           and column2!=column3) as myField1,
       (select COUNT(*) 
          from myTable 
         where column1 != '' 
           and column2=column3) as myField2
  from (select column1,column2,column3,column4,column5 
          from myTable 
         where column1 != '' 
           and column2 != column3) SUB_VIRTUAL_TABLE

Since a varchar2 column in Oracle can never be equal to nor unequal to the empty string, the four column1 predicates aren't doing anything. You probably want to either remove them or do the proper IS NULL/ IS NOT NULL checks. See this discussion on why Oracle treats the empty string as NULL.

Thank you Justin, for the IS NULL tip as well! – Matteo NNZ Aug 03 '15 at 09:57 — Matteo NNZ, Aug 03 '15 at 09:57

Praveen · Answer 2 · 2015-08-03T09:20:17.570

In Oracle '' is treated like null hence the condition != '' can be written like is not null.

Try this:

select SUB_VIRTUAL_TABLE.*, 
(select COUNT(*) from myTable where column1 is not null and column2!=column3) as myField1,
(select COUNT(*) from myTable where column1 is not null and column2=column3) as myField2
from (
    select column1,column2,column3,column4,column5 
    from myTable 
    where column1 is not null and column2 != column3
) SUB_VIRTUAL_TABLE

Getting "ORA-00923: FROM keyword not found where expected" in a Sybase-working query

2 Answers2