4 weeks ago
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
User | Count |
---|---|
98 | |
9 | |
7 | |
5 | |
4 | |
4 | |
3 | |
3 | |
2 | |
2 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.