cancel
Showing results for 
Search instead for 
Did you mean: 

converting SQL query to Hana

Alpha29
Newcomer
0 Kudos
89

Hi experts ,i We have Migrated Our SAP B1 SQL DB TO SAP HANA and i want someone to help me in Writing the following query to HANA SQL :

/* SELECT FROM [dbo].[OITM] T0 */
DECLARE @ToItm AS nvarchar(20)
/* WHERE */
SET @ToItm= /* T0.ItemCode */ '[%0]'
DECLARE @Icode AS nvarchar(20)
DECLARE @Wcode AS nvarchar(20)
SET @Icode = $[INV1.ItemCode]
SET @Wcode = $[INV1.WhsCode]

IF @Icode IN (SELECT ItemCode FROM OBTN)
Begin
(
SELECT T0.ItemName 'اسم الصنف',T0.ItemCode 'كود الصنف',
T0.WhsCode 'المستودع' ,T0.BatchNumber 'رقم الدفعة', T0.Quantity 'الكمية'
FROM IBatchStock T0
WHERE T0.WhsCode = @Wcode
)
END
-----------------------------------------------------------------------------------

If (len(@ToItm)-len(Replace(@ToItm,' ','')) )=0
Begin
SELECT T0.[ItemName],T0.[ItemCode],T1.[ItmsGrpNam],T5.[OnHand] AS 'العريمضي',T6.[OnHand] AS 'الصناعة',T7.[OnHand] AS 'البصيرية',T8.[OnHand] AS 'حفر الباطن', T0.[OnHand] as Stock,T2.PRICE CASH,T3.PRICE CREDIT
From
Oitm T0 INNER JOIN OITB T1 ON T0.[ItmsGrpCod] = T1.[ItmsGrpCod]
INNER JOIN ITM1 T2 ON T0.ITEMCODE = T2.ITEMCODE AND T2.PRICELIST=1
INNER JOIN ITM1 T3 ON T0.ITEMCODE = T3.ITEMCODE AND T3.PRICELIST=2
INNER JOIN OITW T5 on T0.ITEMCODE=T5.ITEMCODE AND T5.WhsCode='0101'
INNER JOIN OITW T6 on T0.ITEMCODE=T6.ITEMCODE AND T6.WhsCode='0201'
INNER JOIN OITW T7 on T0.ITEMCODE=T7.ITEMCODE AND T7.WhsCode='0301'
INNER JOIN OITW T8 on T0.ITEMCODE=T8.ITEMCODE AND T8.WhsCode='0401'
WHERE T0.[ItemName] like N'%' + @ToItm + '%' and T0.[OnHand]<>0 and T0.[ItemName] not like N'%خصم%'
End
If (len(@ToItm)-len(Replace(@ToItm,' ','')) )=2
begin
SELECT T0.[ItemName],T0.[ItemCode],T1.[ItmsGrpNam],T5.[OnHand] AS 'العريمضي',T6.[OnHand] AS 'الصناعة',T7.[OnHand] AS 'البصيرية',T8.[OnHand] AS 'حفر الباطن', T0.[OnHand] as Stock,T2.PRICE CASH,T3.PRICE CREDIT
From
Oitm T0 INNER JOIN OITB T1 ON T0.[ItmsGrpCod] = T1.[ItmsGrpCod]
INNER JOIN ITM1 T2 ON T0.ITEMCODE = T2.ITEMCODE AND T2.PRICELIST=1
INNER JOIN ITM1 T3 ON T0.ITEMCODE = T3.ITEMCODE AND T3.PRICELIST=2
INNER JOIN OITW T5 on T0.ITEMCODE=T5.ITEMCODE AND T5.WhsCode='0101'
INNER JOIN OITW T6 on T0.ITEMCODE=T6.ITEMCODE AND T6.WhsCode='0201'
INNER JOIN OITW T7 on T0.ITEMCODE=T7.ITEMCODE AND T7.WhsCode='0301'
INNER JOIN OITW T8 on T0.ITEMCODE=T8.ITEMCODE AND T8.WhsCode='0401'
WHERE T0.[ItemName] like N'%' + Substring(@ToItm,1,CHARINDEX(' ',@ToItm,1)-1 ) + '%'
and T0.[ItemName] like N'%' + Substring(@ToItm,CHARINDEX(' ',@ToItm,1)+1,(CHARINDEX(' ',@ToItm,CHARINDEX(' ',@ToItm,1)+1))-CHARINDEX(' ',@ToItm,1)-1 ) + '%'
and T0.[ItemName] like N'%' + Substring(@ToItm,(CHARINDEX(' ',@ToItm,CHARINDEX(' ',@ToItm,1)+1) +1),len(@ToItm)-(CHARINDEX(' ',@ToItm,CHARINDEX(' ',@ToItm,1)+1) +1) +1 ) + '%'
and T0.[OnHand]<>0 and T0.[ItemName] not like N'%خصم%'
end
If (len(@ToItm)-len(Replace(@ToItm,' ','')) )=1
Begin
SELECT T0.[ItemName],T0.[ItemCode],T1.[ItmsGrpNam],T5.[OnHand] AS 'العريمضي',T6.[OnHand] AS 'الصناعة',T7.[OnHand] AS 'البصيرية',T8.[OnHand] AS 'حفر الباطن', T0.[OnHand] as Stock,T2.PRICE CASH,T3.PRICE CREDIT
From
Oitm T0 INNER JOIN OITB T1 ON T0.[ItmsGrpCod] = T1.[ItmsGrpCod]
INNER JOIN ITM1 T2 ON T0.ITEMCODE = T2.ITEMCODE AND T2.PRICELIST=1
INNER JOIN ITM1 T3 ON T0.ITEMCODE = T3.ITEMCODE AND T3.PRICELIST=2
INNER JOIN OITW T5 on T0.ITEMCODE=T5.ITEMCODE AND T5.WhsCode='0101'
INNER JOIN OITW T6 on T0.ITEMCODE=T6.ITEMCODE AND T6.WhsCode='0201'
INNER JOIN OITW T7 on T0.ITEMCODE=T7.ITEMCODE AND T7.WhsCode='0301'
INNER JOIN OITW T8 on T0.ITEMCODE=T8.ITEMCODE AND T8.WhsCode='0401'
WHERE T0.[ItemName] like N'%' + Substring(@ToItm,1,CHARINDEX(' ',@ToItm,1)-1 ) + '%'
and T0.[ItemName] like N'%' + Substring(@ToItm,CHARINDEX(' ',@ToItm,1)+1,(Len(@ToItm)-CHARINDEX(' ',@ToItm,1) +1) ) + '%'
and T0.[OnHand]<>0 and T0.[ItemName] not like N'%خصم%'
End



Accepted Solutions (0)

Answers (0)