Query error error: incorrect syntax near "Pivot": SAP Business One HANA

[nikunjmehta2290]

Dear all,

Below query gives error like : SAP DBTech JDBC: [257]: sql syntax error: incorrect syntax near "Pivot": line 23 col 1 (at pos 495)

SELECT "CustCode"

as "Custcode",

"1" as "Jan",

"2" as "Feb",

"3" as "Mar",

"4" as "Apr",

"5" as "May",

"6" as "june",

"7" as "July",

"8" as "Aug",

"9" as "Sept",

"10" as "Oct",

"11" as "Nov",

"12" as "Dec"

From

(select T0."CardCode" as "Custcode", sum(T0."PlannedQty") as "PlanQty",month(T0."DueDate") as "Month"

from OWOR T0

where T0."Status" NOT IN ( 'C' ) and year(T0."DueDate") = 2021 group by T0."CardCode",T0."DueDate" )s

Pivot

(sum("PlanQty") for month IN ("1","2","3","4","5","6","7","8","9","10","11","12")) P

order by "CustCode"

Regards,

Nikunj

[nikunjmehta2290 · Accepted solution]

Corrected query

Select "ItemCode", SUM("JAN") as "Jan", SUM("Feb") as "Feb", SUM("Mar") as "Mar" ,

SUM("April") as "April", SUM("May") as "May", SUM("June") as "June" ,

SUM("July") as "July",SUM("Aug") as "Aug", SUM("Sep") as "Sep" ,

SUM("Oct") as "Oct", SUM("Nov") as "Nov", SUM("Dec") as "Dec"

from (

select

T1."ItemCode",

(case when month(T0."DocDate") = '1' then Sum(T1."Quantity") else NULL end) as "JAN",

(case when month(T0."DocDate") = '2' then Sum(T1."Quantity") else NULL end) as "Feb",

(case when month(T0."DocDate") = '3' then Sum(T1."Quantity") else NULL end) as "Mar",

(case when month(T0."DocDate") = '4' then Sum(T1."Quantity") else NULL end) as "April",

(case when month(T0."DocDate") = '5' then Sum(T1."Quantity") else NULL end) as "May",

(case when month(T0."DocDate") = '6' then Sum(T1."Quantity") else NULL end) as "June",

(case when month(T0."DocDate") = '7' then Sum(T1."Quantity") else NULL end) as "July",

(case when month(T0."DocDate") = '8' then Sum(T1."Quantity") else NULL end) as "Aug",

(case when month(T0."DocDate") = '9' then Sum(T1."Quantity") else NULL end) as "Sep",

(case when month(T0."DocDate") = '10' then Sum(T1."Quantity") else NULL end) as "Oct",

(case when month(T0."DocDate") = '11' then Sum(T1."Quantity") else NULL end) as "Nov",

(case when month(T0."DocDate") = '12' then Sum(T1."Quantity") else NULL end) as "Dec"

from OINV T0

INNER JOIN INV1 T1 ON T0."DocEntry" = T1."DocEntry"

inner join OCRD on OCRD."CardCode"= T0."CardCode"

where year(T0."DocDate") = 2022 --and T1."ItemCode" = 1

group by T1."ItemCode", T0."DocDate", T1."Quantity"

) as T89 GROUP BY "ItemCode"

[OlehFabri]

Hi Nikunj,

HANA does not have a built-in PIVOT function, hence your query will work in MS SQL, but not in SAP HANA. You may try the approach as explained in the blog here as a workaround:

https://blogs.sap.com/2019/04/21/how-to-pivotunpivot-in-sap-hana/

[neilos]

Hi Nikunj,

Glancing over it, I can't see any syntax errors...

I copied and pasted it as you have it, removed blank lines and some other little amends.
Executing it doesn't give any errors either, it returns results!

SELECT "CustCode"
as "Custcode",
"1" as "Jan",
"2" as "Feb",
"3" as "Mar",
"4" as "Apr",
"5" as "May",
"6" as "june",
"7" as "July",
"8" as "Aug",
"9" as "Sept",
"10" as "Oct",
"11" as "Nov",
"12" as "Dec"
From
(select T0."CardCode" as "Custcode", sum(T0."PlannedQty") as "PlanQty", month(T0."DueDate") as "Month"
from OWOR T0
where T0."Status" NOT IN ('C') and year(T0."DueDate") = 2021 group by T0."CardCode",T0."DueDate" )s
Pivot
(sum("PlanQty") for month IN ("1","2","3","4","5","6","7","8","9","10","11","12")) P
order by "CustCode"

[nikunjmehta2290]

It's gives me same error while executing into SAP Business One HANA studio.

[neilos]

Apologies, I didn't read it was for HANA

AFAIK (which isn't a lot) SAP HANA can't support PIVOT

This may help with some ideas.
https://www.owler.com/reports/sap-se/how-to-pivot-unpivot-in-sap-hana/1555860723945