topic SAL HANA SQL Query question in Application Development and Automation Discussions

SAL HANA SQL Query question

former_member820000 — Mon, 05 Sep 2022 05:05:33 GMT

HI all

i have code for Some Monthly sales analysis Report.

i seen this on

but some point reason this code is not working

seems getdata is strange

do you guys and ladies know how to fix it?

SELECT T0."CardCode", T0."CardName",

(SUM(T1."Debit") - sum(T1."Credit")) AS "2017",

((SUM(T1."Debit") - sum(T1."Credit"))/12) AS "2017Average Sales",

(SUM(T2."Debit") - sum(T2."Credit")) AS "2018",

((SUM(T2."Debit") - sum(T2."Credit"))/Month(GETDATE()))AS "2018 Average Sales"

FROM OCRD T0

LEFT JOIN JDT1 T1

ON T1."ShortName" = T0."CardCode"

AND YEAR(T1."DueDate") =2017 AND T1."TransType" in('13','14')

LEFT JOIN JDT1 T2 ON T2."ShortName" = T0."CardCode" AND Year(T2."DueDate") = 2018

AND T2."TransType" in ('13','14')

WHERE T0."CardType" = 'C'

GROUP BY T0."CardCode", T0."CardName"

Vitaliy-R — Tue, 06 Sep 2022 10:27:06 GMT

I understand you have an SAP HANA database. An example you share is for the MS SQL Server database (as stated as well in the article you linked).

If I got it right then the equivalent of GetDate from MS SQL should be CURRENT_DATE in HANA.

Regards.

former_member820000 — Wed, 07 Sep 2022 02:52:41 GMT

I tried but seems still error

i'm really beginner so i don't know how to solve this

SELECT T0."CardCode", T0."CardName",

(SUM(T1."Debit") - sum(T1."Credit")) AS "2017",

((SUM(T1."Debit") - sum(T1."Credit"))/12) AS "2017Average Sales",

(SUM(T2."Debit") - sum(T2."Credit")) AS "2018",

((SUM(T2."Debit") - sum(T2."Credit"))/Month(CURRENT_DATE())) AS "2018 Average Sales"

FROM

OCRD T0

LEFT JOIN JDT1 T1

ON T1."ShortName" = T0."CardCode"

AND YEAR(T1."DueDate") =2017 AND T1."TransType" in('13','14')

LEFT JOIN JDT1 T2 ON T2."ShortName" = T0."CardCode" AND Year(T2."DueDate") = 2018

AND T2."TransType" in ('13','14') WHERE T0."CardType" = 'C' GROUP BY T0."CardCode", T0."CardName"

Vitaliy-R — Thu, 08 Sep 2022 15:50:57 GMT

CURRENT_DATE is a function, but it does not require () at the end. Try that.