CODEWITHSUNDEEP
sqloracle-database
Mohammad Sultan
Mohammad Sultan

Reputation: 15

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

Upvotes: 1

Views: 94

Answers (2)

David דודו Markovitz
David דודו Markovitz

Reputation: 44911

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

Upvotes: 0

Zhenora
Zhenora

Reputation: 68

You make a mistake in alias of sub query As LastBill - delete the as. Correct version should look like

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

Upvotes: 2

Related Questions