Skip to Content
0
Jan 07, 2023 at 12:43 AM

AR Query error

101 Views

Experts,

I'm writing a AR Query and got the starting point from the forum.

This is for SAP B1 Hana and When I take the date ranges (0-30) away the query works, as soon as I put this in a get an error on near the WHERE and ISNULL... Any insight would be helpful!

SELECT T1."CardCode", T1."CardName", T1."CreditLine", T0."RefDate", T0."Ref1",
CASE 
WHEN T0."TransType"=13 THEN 'Invoice'
WHEN T0."TransType"=14 THEN 'Credit Note'
WHEN T0."TransType"=30 THEN 'Journal'
WHEN T0."TransType"=24 THEN 'Receipt'
END
 AS "Document Type",
T0."DueDate", (T0."Debit"- T0."Credit") AS "Balance"

,ISNULL((SELECT (T0."Debit"-T0."Credit") AS "Future"
WHERE DateDiff(day, T0."DueDate",'[%1]')<=-1),0)  

FROM JDT1 
T0 INNER JOIN OCRD T1 ON T0."ShortName" = T1."CardCode"
WHERE (T0."MthDate" IS NULL OR T0."MthDate" > [%1]) 
AND T0."RefDate" <= [%1] AND T1."CardType" = 'C'
ORDER BY T1."CardCode", T0."DueDate", T0."Ref1"