在 Sybase 工作查询中获取"ORA-00923: FROM keyword not found where expected"

Question

我已经构建了以下查询在Sybase引擎上运行良好:

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

但是，在Oracle引擎上运行相同的查询将引发以下异常:

ORA-00923: FROM keyword not found where expected

可以请任何人帮助修复这个语法吗?非常感谢。注:表和列都存在，当引擎是Sybase时，查询确实运行得很好。

预期结果如下所示:

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

Answer 1

在Oracle中不使用as关键字来别名子查询。你只需要指定别名。你可能需要在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

由于Oracle中的varchar2列永远不能等于或不等于空字符串，因此四个column1谓词没有做任何事情。您可能想要删除它们或做适当的IS NULL/IS NOT NULL检查。

Answer 2

在Oracle中''被视为null，因此条件!= ''可以写成is not null。

试试这个:

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