SQL to Hana query

[ndan2022]

Hello,

I have an SQL query for a store procedure which I would like to convert to Hana. I have tried the Converter Tool from B1UP but it is failing to convert and this query is quite complex.

Running B1 v10 for SAP Hana FP2208.

Many thanks in advanced.

ALTER PROCEDURE [dbo].[ALP_SAP]

-- Add the parameters for the stored procedure here

@BPFrom nvarchar(20) = '',

@BPTo nvarchar(20) = '',

@AgeingDate datetime,

@Property54 nvarchar(3) = 'Y'

AS

BEGIN

SET NOCOUNT ON;

select

--t0.fathercard, t0.cardcode,

--(case Isnull(t0.fathercard, '') WHEN '' THEN t0.cardcode else t0.fathercard END),

'INV' 'Type', t0.DocNum, t0.docdate, t0.DocDueDate, (t0.DocTotal - t0.PaidSum) 'Outstanding', (t0.DocTotalFC - t0.PaidSumFC) 'OutstandingFC',

datediff(MONTH, t0.docdate, @AgeingDate) AS AgedDays,

IsNull((select t0.DocTotal - t0.PaidSum from OINV t9 where t9.DocNum = t0.docnum and datediff(MONTH, t0.docdate, @AgeingDate) < 0),0) 'NotCurrent',

IsNull((select t0.DocTotal - t0.PaidSum from OINV t9 where t9.DocNum = t0.docnum and datediff(MONTH, t0.docdate, @AgeingDate) = 0),0) 'Current',

IsNull((select t0.DocTotal - t0.PaidSum from OINV t9 where t9.DocNum = t0.docnum and datediff(MONTH, t0.docdate, @AgeingDate) = 1),0) '31-60',

IsNull((select t0.DocTotal - t0.PaidSum from OINV t9 where t9.DocNum = t0.docnum and datediff(MONTH, t0.docdate, @AgeingDate) = 2),0) '61-90',

IsNull((select t0.DocTotal - t0.PaidSum from OINV t9 where t9.DocNum = t0.docnum and datediff(MONTH, t0.docdate, @AgeingDate) >= 3),0) '90+',

IsNull((select t0.DocTotalFC - t0.PaidSumFC from OINV t9 where t9.DocNum = t0.docnum and datediff(MONTH, t0.docdate, @AgeingDate) < 0),0) 'NotCurrentFC',

IsNull((select t0.DocTotalFC - t0.PaidSumFC from OINV t9 where t9.DocNum = t0.docnum and datediff(MONTH, t0.docdate, @AgeingDate) = 0),0) 'CurrentFC',

IsNull((select t0.DocTotalFC - t0.PaidSumFC from OINV t9 where t9.DocNum = t0.docnum and datediff(MONTH, t0.docdate, @AgeingDate) = 1),0) '31-60FC',

IsNull((select t0.DocTotalFC - t0.PaidSumFC from OINV t9 where t9.DocNum = t0.docnum and datediff(MONTH, t0.docdate, @AgeingDate) = 2),0) '61-90FC',

IsNull((select t0.DocTotalFC - t0.PaidSumFC from OINV t9 where t9.DocNum = t0.docnum and datediff(MONTH, t0.docdate, @AgeingDate) >= 3),0) '90+FC',

isnull(t0.NumAtCard, '') 'YourReference', 'Inv ' + cast(t0.DocNum as nvarchar(50)) 'OurReference',

t1.cardcode, t1.CardName, t3.Address, t3.Street, t3.Block, t3.City, t3.County, t3.country, t3.ZipCode,

t2.PymntGroup, t4.SlpName, t4.SlpCode, t4.U_Mobile, t1.Currency, (CASE t1.QryGroup54 WHEN 'Y' THEN '' else IsNull(t1.E_Mail,'') end) 'BPEmail',

t1.Balance

from OINV t0 inner join OCRD t1 --on t1.CardCode = isnull(t0.fathercard, t0.cardcode)

--ON t1.CardCode = (case Isnull(t0.fathercard, t0.cardcode) WHEN '' THEN t0.cardcode else t0.fathercard END)

ON t1.CardCode = (case Isnull(t0.fathercard, '') WHEN '' THEN t0.cardcode else t0.fathercard END)

inner join OCTG t2 on t2.GroupNum = t1.GroupNum

inner join CRD1 t3 on (t3.CardCode = t1.CardCode and t3.Address = t1.BillToDef and t3.AdresType = 'B')

inner join OSLP t4 on t4.SlpCode = t1.SlpCode

where

t0.DocStatus = 'O'

AND

t1.CardType='C'

--AND (isnull(t0.fathercard, t0.cardcode) >= @BPFrom AND isnull(t0.fathercard, t0.cardcode) <= @BPTo)

AND (case Isnull(t0.fathercard, '') WHEN '' THEN t0.cardcode else t0.fathercard END) >= @BPFrom

AND (case Isnull(t0.fathercard, '') WHEN '' THEN t0.cardcode else t0.fathercard END) <= @BPto

AND

t0.DocDate <= @AgeingDate

AND t1.QryGroup54 <> 'Y' and t1.QryGroup55 <> 'N'

--and t1.QryGroup54 = @Property54

--and t0.DocNum not in ('321483','335544')

group

by t0.DocNum, t0.docdate, t0.DocDueDate, t1.cardcode, t1.CardName, t3.Address, t3.Street,

t3.Block, t3.City, t3.County, t3.country, t3.ZipCode,

t2.PymntGroup, t4.SlpName, t4.SlpCode, t0.DocTotal, t0.DocTotalFC, t0.PaidSum, t0.PaidSumFC,

t0.NumAtCard, t4.U_Mobile,

t1.Currency, t1.E_Mail,

t1.Balance, t1.QryGroup54

--,t0.fathercard, t0.cardcode

union all

select

'Crn', t0.DocNum, t0.docdate, t0.DocDueDate, (t0.DocTotal - t0.PaidSum) * -1, (t0.DocTotalFC - t0.PaidSumFC) * -1,

datediff(month, t0.docdate, @AgeingDate) AS AgedDays,

IsNull((select (t0.DocTotal - t0.PaidSum) * -1 from ORIN t9 where t9.DocNum = t0.docnum and datediff(MONTH, t0.docdate, @AgeingDate) < 0),0),

IsNull((select (t0.DocTotal - t0.PaidSum) * -1 from ORIN t9 where t9.DocNum = t0.docnum and datediff(MONTH, t0.docdate, @AgeingDate) = 0),0),

IsNull((select (t0.DocTotal - t0.PaidSum) * -1 from ORIN t9 where t9.DocNum = t0.docnum and datediff(MONTH, t0.docdate, @AgeingDate)= 1),0),

IsNull((select (t0.DocTotal - t0.PaidSum) * -1 from ORIN t9 where t9.DocNum = t0.docnum and datediff(MONTH, t0.docdate, @AgeingDate) = 2),0),

IsNull((select (t0.DocTotal - t0.PaidSum) * -1 from ORIN t9 where t9.DocNum = t0.docnum and datediff(MONTH, t0.docdate, @AgeingDate) >= 3),0),

IsNull((select (t0.DocTotalFC - t0.PaidSumFc) * -1 from ORIN t9 where t9.DocNum = t0.docnum and datediff(MONTH, t0.docdate, @AgeingDate) < 0),0),

IsNull((select (t0.DocTotalFC - t0.PaidSumFc) * -1 from ORIN t9 where t9.DocNum = t0.docnum and datediff(MONTH, t0.docdate, @AgeingDate) = 0),0),

IsNull((select (t0.DocTotalFC - t0.PaidSumFc) * -1 from ORIN t9 where t9.DocNum = t0.docnum and datediff(MONTH, t0.docdate, @AgeingDate)= 1),0),

IsNull((select (t0.DocTotalFC - t0.PaidSumFc) * -1 from ORIN t9 where t9.DocNum = t0.docnum and datediff(MONTH, t0.docdate, @AgeingDate) = 2),0),

IsNull((select (t0.DocTotalFC - t0.PaidSumFc) * -1 from ORIN t9 where t9.DocNum = t0.docnum and datediff(MONTH, t0.docdate, @AgeingDate) >= 3),0),

t0.NumAtCard, 'Crn ' + cast(t0.DocNum as nvarchar(50)),

t1.cardcode, t1.CardName, t3.Address, t3.Street, t3.Block, t3.City, t3.County, t3.country, t3.ZipCode,

t2.PymntGroup, t4.SlpName, t4.SlpCode, t4.U_Mobile, t1.Currency, (CASE t1.QryGroup54 WHEN 'Y' THEN '' else IsNull(t1.E_Mail,'') end) 'BPEmail',

t1.Balance

from

ORIN t0 inner join OCRD t1 --on t1.CardCode = isnull(t0.fathercard, t0.cardcode)

--ON t1.CardCode = (case Isnull(t0.fathercard, t0.cardcode) WHEN '' THEN t0.cardcode else t0.fathercard END)

ON t1.CardCode = (case Isnull(t0.fathercard, '') WHEN '' THEN t0.cardcode else t0.fathercard END)

inner join OCTG t2 on t2.GroupNum = t1.GroupNum

inner join CRD1 t3 on (t3.CardCode = t1.CardCode and t3.Address = t1.BillToDef and t3.AdresType = 'B')

inner join OSLP t4 on t4.SlpCode = t1.SlpCode

where

t0.DocStatus = 'O'

AND

t1.CardType='C'

--AND (isnull(t0.fathercard, t0.cardcode) >= @BPFrom AND isnull(t0.fathercard, t0.cardcode) <= @BPTo)

AND (case Isnull(t0.fathercard, '') WHEN '' THEN t0.cardcode else t0.fathercard END) >= @BPFrom

AND (case Isnull(t0.fathercard, '') WHEN '' THEN t0.cardcode else t0.fathercard END) <= @BPto

--and t1.QryGroup54 = @Property54

--and t0.DocNum in ('620195','622827','824417')

AND t0.DocDate <= @AgeingDate

AND t1.QryGroup54 <> 'Y' and t1.QryGroup55 <> 'N'

group

by t0.DocNum, t0.docdate, t0.DocDueDate, t1.cardcode, t1.CardName, t3.Address, t3.Street,

t3.Block, t3.City, t3.County, t3.country, t3.ZipCode,

t2.PymntGroup, t4.SlpName, t4.SlpCode, t0.DocTotal, t0.DocTotalfc, t0.PaidSum, t0.PaidSumFc,

t0.NumAtCard, t4.U_Mobile, t1.Currency, t1.E_Mail,

t1.Balance, t1.QryGroup54

union all

select

'UAR', t0.DocNum, t0.docdate, t0.DocDueDate, t0.OpenBal * -1, t0.OpenBal * -1,

datediff(month, t0.docdate, @AgeingDate) AS AgedDays,

IsNull((select (t0.OpenBal) * -1 from ORIN t9 where t9.DocNum = t0.docnum and datediff(MONTH, t0.docdate, @AgeingDate) < 0),0),

IsNull((select (t0.OpenBal) * -1 from ORIN t9 where t9.DocNum = t0.docnum and datediff(MONTH, t0.docdate, @AgeingDate) = 0),0),

IsNull((select (t0.OpenBal) * -1 from ORIN t9 where t9.DocNum = t0.docnum and datediff(MONTH, t0.docdate, @AgeingDate)= 1),0),

IsNull((select (t0.OpenBal) * -1 from ORIN t9 where t9.DocNum = t0.docnum and datediff(MONTH, t0.docdate, @AgeingDate) = 2),0),

IsNull((select (t0.OpenBal) * -1 from ORIN t9 where t9.DocNum = t0.docnum and datediff(MONTH, t0.docdate, @AgeingDate) >= 3),0),

IsNull((select (t0.OpenBalfc) * -1 from ORIN t9 where t9.DocNum = t0.docnum and datediff(MONTH, t0.docdate, @AgeingDate) < 0),0),

IsNull((select (t0.OpenBalfc) * -1 from ORIN t9 where t9.DocNum = t0.docnum and datediff(MONTH, t0.docdate, @AgeingDate) = 0),0),

IsNull((select (t0.OpenBalfc) * -1 from ORIN t9 where t9.DocNum = t0.docnum and datediff(MONTH, t0.docdate, @AgeingDate)= 1),0),

IsNull((select (t0.OpenBalfc) * -1 from ORIN t9 where t9.DocNum = t0.docnum and datediff(MONTH, t0.docdate, @AgeingDate) = 2),0),

IsNull((select (t0.OpenBalfc) * -1 from ORIN t9 where t9.DocNum = t0.docnum and datediff(MONTH, t0.docdate, @AgeingDate) >= 3),0),

t0.CounterRef, 'Unallocated Receipt ',

t1.cardcode, t1.CardName, t3.Address, t3.Street, t3.Block, t3.City, t3.County, t3.country, t3.ZipCode,

t2.PymntGroup, t4.SlpName, t4.SlpCode, t4.U_Mobile, t1.Currency, (CASE t1.QryGroup54 WHEN 'Y' THEN '' else IsNull(t1.E_Mail,'') end) 'BPEmail',

t1.Balance

from ORCT t0 inner join OCRD t1 on t1.CardCode = t0.CardCode

inner join OCTG t2 on t2.GroupNum = t1.GroupNum

inner join CRD1 t3 on (t3.CardCode = t1.CardCode and t3.Address = t1.BillToDef and t3.AdresType = 'B')

inner join OSLP t4 on t4.SlpCode = t1.SlpCode

where

t0.OpenBal > 0

AND

t1.CardType='C'

and

(t1.CardCode >= @BPFrom AND t1.CardCode <= @BPTo)

AND t0.DocDate <= @AgeingDate

AND t1.QryGroup54 <> 'Y' and t1.QryGroup55 <> 'N'

--and t1.QryGroup54 = @Property54

--and t0.DocNum in ('620195','622827','824417')

union all

select

'ADJ' 'Type', t0.TransId, t0.refdate, t0.DueDate, (t0.BalDueDeb - t0.BalDueCred) 'Outstanding', (t0.BalFcDeb - t0.BalFcCred),

datediff(month, t0.refdate, @AgeingDate) AS AgedDays,

IsNull((select sum(t9.BalDueDeb) - SUM(t9.BalDueCred) from JDT1 t9 where t9.TransId = t0.TransId AND t9.Line_ID = t0.Line_ID and datediff(MONTH, t0.RefDate, @AgeingDate) < 0),0) 'NotCurrent',

IsNull((select sum(t9.BalDueDeb) - SUM(t9.BalDueCred) from JDT1 t9 where t9.TransId = t0.TransId AND t9.Line_ID = t0.Line_ID and datediff(MONTH, t0.RefDate, @AgeingDate) =0),0) 'Current',

IsNull((select sum(t9.BalDueDeb) - SUM(t9.BalDueCred) from JDT1 t9 where t9.TransId = t0.TransId AND t9.Line_ID = t0.Line_ID and datediff(MONTH, t0.RefDate, @AgeingDate)= 1),0) '31-60',

IsNull((select sum(t9.BalDueDeb) - SUM(t9.BalDueCred) from JDT1 t9 where t9.TransId = t0.TransId AND t9.Line_ID = t0.Line_ID and datediff(MONTH, t0.RefDate, @AgeingDate) = 2),0) '61-90',

IsNull((select sum(t9.BalDueDeb) - SUM(t9.BalDueCred) from JDT1 t9 where t9.TransId = t0.TransId AND t9.Line_ID = t0.Line_ID and datediff(MONTH, t0.RefDate, @AgeingDate) >= 3),0) '90+',

IsNull((select sum(t9.BalFcDeb) - SUM(t9.BalFcCred) from JDT1 t9 where t9.TransId = t0.TransId AND t9.Line_ID = t0.Line_ID and datediff(MONTH, t0.RefDate, @AgeingDate) < 0),0) 'NotCurrent',

IsNull((select sum(t9.BalFcDeb) - SUM(t9.BalFcCred) from JDT1 t9 where t9.TransId = t0.TransId AND t9.Line_ID = t0.Line_ID and datediff(MONTH, t0.RefDate, @AgeingDate) = 0),0) 'Current',

IsNull((select sum(t9.BalFcDeb) - SUM(t9.BalFcCred) from JDT1 t9 where t9.TransId = t0.TransId AND t9.Line_ID = t0.Line_ID and datediff(MONTH, t0.RefDate, @AgeingDate)= 1),0) '31-60',

IsNull((select sum(t9.BalFcDeb) - SUM(t9.BalFcCred) from JDT1 t9 where t9.TransId = t0.TransId AND t9.Line_ID = t0.Line_ID and datediff(MONTH, t0.RefDate, @AgeingDate) = 2),0) '61-90',

IsNull((select sum(t9.BalFcDeb) - SUM(t9.BalFcCred) from JDT1 t9 where t9.TransId = t0.TransId AND t9.Line_ID = t0.Line_ID and datediff(MONTH, t0.RefDate, @AgeingDate) >= 3),0) '90+',

'' as 'YourReference', 'JE ' + cast(t0.TransId as nvarchar(50)) 'OurReference',

t1.cardcode, t1.CardName, t3.Address, t3.Street, t3.Block, t3.City, t3.County,

t3.country, t3.ZipCode,

t2.PymntGroup, t4.SlpName, t4.SlpCode, t4.U_Mobile, t1.Currency, (CASE t1.QryGroup54 WHEN 'Y' THEN '' else IsNull(t1.E_Mail,'') end) 'BPEmail',

t1.Balance

from JDT1 t0 inner join OCRD t1 on t1.CardCode = t0.ShortName

inner join OCTG t2 on t2.GroupNum = t1.GroupNum

inner join CRD1 t3 on (t3.CardCode = t1.CardCode and t3.Address = t1.BillToDef and t3.AdresType = 'B')

inner join OSLP t4 on t4.SlpCode = t1.SlpCode

where

--t0.DocStatus = 'O' AND

(t0.ShortName >= @BPFrom AND t0.ShortName <= @BPTo)

AND

t1.CardType='C'

--and t1.QryGroup54 = @Property54

AND (t0.BalDueDeb - t0.BalDueCred <> 0)

AND t0.TransType = 30

--and t0.TransId in ('620195','622827','824417')

AND t0.RefDate <= @AgeingDate

AND t1.QryGroup54 <> 'Y' and t1.QryGroup55 <> 'N'

group

by t0.TransId, t0.RefDate, t0.DueDate, t1.cardcode, t1.CardName, t3.Address, t3.Street,

t3.Block, t3.City, t3.County, t3.country, t3.ZipCode,

t2.PymntGroup, t4.SlpName, t4.SlpCode,

t0.BalDueDeb, t0.BalDueCred, t0.BalFcDeb, t0.BalFcCred, t4.U_Mobile, t1.Currency, t1.E_Mail,

t0.Line_ID,

t1.Balance, t1.QryGroup54

ORDER BY t0.docdate

END

[ndan2022]

Hello experts, can someone assist with this?