Oracle Select语句中的错误

Question

我在表中有一个字段BillNumber，可以重复使用票据号，我想得到某个票据的最后一行，我使用了以下语句，它在Server上工作得很好，但在Oracle上却不起作用：

select MB.* 
from SADAD.MAINBILLDETAILS AS MB 
   inner join (select BILLNUMBER, Max(CREATEDON) AS LastDate 
               from SADAD.MAINBILLDETAILS 
               group by BILLNUMBER) As LastBill On MB.BILLNUMBER = LastBill.BILLNUMBER AND MB.CREATEDON = LastBill.LastDate 
where not ISREJECTED Is Null 
  and SubStr(MB.BILLNUMBER,1,3) ='301' 
  and CREATEDON Between To_Date('2016/07/19', 'YYYY/MM/DD') 
  and To_Date('2016/10/19', 'YYYY/MM/DD') 
order by CREATEDON

错误消息：

ORA-00933:命令未正确结束

Answer 1

奇怪的是，Oracle不接受关键字作为表别名的，只接受列别名。

select * from dual as d;

ORA-00933: SQL command not properly ended
00933. 00000 -  "SQL command not properly ended"
*Cause:    
*Action:
Error at Line: 1 Column: 20

select * from dual d;

DUMMY
-----
X

Answer 2

您在子查询As LastBill的别名中犯了错误--删除as。正确的版本应该是

Select MB.*  
  From SADAD.MAINBILLDETAILS  MB 
 Inner Join (Select BILLNUMBER, Max(CREATEDON) AS LastDate 
             From SADAD.MAINBILLDETAILS 
            Group By BILLNUMBER) LastBill On MB.BILLNUMBER = LastBill.BILLNUMBER AND MB.CREATEDON = LastBill.LastDate 
Where Not ISREJECTED Is Null 
  And SubStr(MB.BILLNUMBER,1,3) ='301' 
  And CREATEDON Between To_Date('2016/07/19', 'YYYY/MM/DD') And To_Date('2016/10/19', 'YYYY/MM/DD') 
Order By CREATEDON