Skip to Content

Pivot Table in Hana

Dear Expert,

                   I am trying the following query in Hana Studio.....

SELECT * from (select T0."ItemCode" as Code, T1."WhsCode" as Whs, sum(T1."OnHand") as Stock FROM OITM T0  INNER JOIN OITW

T1 ON T0."ItemCode" = T1."ItemCode" INNER JOIN OWHS T2 ON T1."WhsCode" = T2."WhsCode" WHERE T2."U_Type_Of_Whs" = 'STATION' 

and T1."WhsCode" <> 'HO'  group by T0."ItemCode",T1."WhsCode" ) P Pivot(Sum(Stock) for Code in (PMS001,AGO001,IK001)

) as pvt

and I am receiving the error incorrect syntax near "Pivot": line

Please suggest the solution.

regards,

PankajK

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

4 Answers

  • Sep 15, 2014 at 09:12 AM

    Hi,

    Try this:

    SELECT * from (select T0."ItemCode" as Code, T1."WhsCode" as Whs, sum(T1."OnHand") as Stock FROM OITM T0  INNER JOIN OITW

    T1 ON T0."ItemCode" = T1."ItemCode" INNER JOIN OWHS T2 ON T1."WhsCode" = T2."WhsCode" WHERE T2."U_Type_Of_Whs" = 'STATION' 

    and T1."WhsCode" <> 'HO'  group by T0."ItemCode",T1."WhsCode" ) P

    Pivot(Sum(Stock) for Code in (PMS001,AGO001,IK001)

    )) as pvt

    Thanks & Regards,

    Nagarajan

    Add comment
    10|10000 characters needed characters exceeded

  • Sep 15, 2014 at 09:55 AM

    Hi,

    I copied your query and justr removed the U_Type_of_whs because I don't have this field and the query is running fine.

    Is the name of field correct?

    If it's correct add the U_Type_of_whs in the group by

     

    T2."U_Type_Of_Whs" = 'STATION'   and

    I hope it helps

    Add comment
    10|10000 characters needed characters exceeded

  • Nov 18, 2014 at 07:03 AM

    Hi

    try putting the itemcodes in square brackets.

    SELECT * from (select T0."ItemCode" as Code, T1."WhsCode" as Whs, sum(T1."OnHand") as Stock FROM OITM T0  INNER JOIN OITW

    T1 ON T0."ItemCode" = T1."ItemCode" INNER JOIN OWHS T2 ON T1."WhsCode" = T2."WhsCode" WHERE T2."U_Type_Of_Whs" = 'STATION'

    and T1."WhsCode" <> 'HO'  group by T0."ItemCode",T1."WhsCode" ) P

    Pivot(Sum(Stock) for Code in ([PMS001],[AGO001],[IK001])

    ) as pvt

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    May 28, 2015 at 02:13 AM

    Hi Pankaj,

    I am also getting the same error "

    SAP DBTech JDBC: [257]: sql syntax error: incorrect syntax near "PIVOT" , did you resolve it ?

    If yes , can you pls tell me the steps to resolve the issue.

    Thanks,

    Hemapriya


    Add comment
    10|10000 characters needed characters exceeded