SQL to SAP HANA query convert SAP Business One HANA

[nikunjmehta2290]

Dear all,

Need to convert below query for HANA. I hope below query gives me all logs details for particular SAP user.

Declare @FromDate as Datetime
Declare @ToDate as DateTime
Declare @User as numeric
Set @FromDate = (Select min(S1.UpdateDate) From ACRD S1 
Where S1.UpdateDate >= '[%0]')
Set @ToDate = (Select max(S2.UpdateDate) From ACRD S2 
Where S2.UpdateDate <= '[%1]')
Set @user = (select max(S3.USERID) From OUSR S3 
Where S3.U_NAME='[%2]')

Select 
(CASE WHEN A0.ObjType = '-2' THEN 'Opening Balance'
WHEN A0.ObjType = '22' THEN 'Purchase Order'
WHEN A0.ObjType = '23' THEN 'Sales Quotations'
WHEN A0.ObjType = '17' THEN 'Sales Orders'
WHEN A0.ObjType = '13' THEN 'AR Invoice' 
WHEN A0.ObjType = '14' THEN 'AR Credit Memo'
WHEN A0.ObjType = '15' THEN 'Delivery' 
WHEN A0.ObjType = '16' THEN 'Return'
WHEN A0.ObjType = '18' THEN 'AP Invoice'
WHEN A0.ObjType = '19' THEN 'AP Credit Memo' 
WHEN A0.ObjType = '20' THEN 'Goods Receipt PO' 
WHEN A0.ObjType = '202' THEN 'Production Order' 
WHEN A0.ObjType = '21' THEN 'Goods Return' 
WHEN A0.ObjType = '24' THEN 'Incoming Payments' 
WHEN A0.ObjType = '30' THEN 'Journal Entry'
WHEN A0.ObjType = '46' THEN 'Outgoing Payments' 
WHEN A0.ObjType = '58' THEN 'Stock Posting' 
WHEN A0.ObjType = '59' THEN 'Goods Receipt/Receipt from production' 
WHEN A0.ObjType = '60' THEN 'Goods Issue/Issue from Production' 
WHEN A0.ObjType = '67' THEN 'InventoryTransfer'
WHEN A0.ObjType = '69' THEN 'Landed Costs' 
WHEN A0.ObjType = '162' THEN 'Inventory Revaluation' 
WHEN A0.ObjType = '140000009' THEN 'Outgoing Excise Invoice' 
WHEN A0.ObjType = '140000010' THEN 'Incoming Excise Invoice' ELSE 'NULLVALUE' END) AS 'Transaction Name',
* From ADOC A0,ADO1 A1
Where
A0.DocEntry=A1.DocEntry and
A0.loginstanc <> 1 and
A0.UserSign2=@User and
A0.UpdateDate >=@FromDate and
A0.UpdateDate <=@Todate

[nidhisingh14]

Hi nik2290

Please check the "How to Convert SQL from the Microsoft SQL Server Database to the SAP HANA Database" from the help page: https://help.sap.com/docs/SAP_BUSINESS_ONE/83196e2e80d941d798668e7c34568832/5a96195057e845719865ac24...

Kind Regards
Nidhi Singh
SAP Business One Support