Mohammad Sultan Mohammad Sultan - 1 month ago 8
SQL Question

Error in Oracle Select statement

I have a field BillNumber in a table where bill numbers can be repeated, and i want to get the last row of a certain bill, I used this statement which works fine on Sql Server but not on Oracle database:

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


Error message:


ORA-00933: Command not properly ended

Answer

Strangely enough, Oracle does not accept the keyword AS for table aliases, only for column aliases.

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