Pivot Table in Hana

[former_member614487]

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

[former_member49960]

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

[former_member184146]

Hi Hema,

Pivot is not supported in HANA , you can achieve that using the case statement  .

--Manish

[former_member49960]

Thanks Manish for the information.

Do u know how can I achieve NVARCHAR(MAX) ? , Really big string ( dynamic query).

Thanks,

Hema

[former_member184146]

Hi ,

Please post a new thread related to new query as this thread is  created by someone else and not related to your question.

--Manish

[malcolm_lamour]

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

[former_member186712]

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

[former_member614487]

The Query is fine.R u Trying in SAP B1 Hana Studio..

Try it in Hana Studio or SAP B1 HANA application.

regards,

PankajK.

[former_member186712]

Sorry but I dont have Hana.

I was running the query in the SQL

[kothandaraman_nagarajan]

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

[former_member614487]

Hello Mr. Nagarajan,

                                   I tried the query and still getting the same error.

regards,

PankajK

[kothandaraman_nagarajan]

Hi,

Try this query and working in SAP B1 query generator:

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

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

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

) as pvt

OR Try this query in your DB

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

[former_member614487]

Hi Mr. Nagarajan,

                          I appreciate your efforts.But still both of the query is not working in SAP B1 application or in Hana Studio.

By The way I am Using SAP B1 Hana Application.This pivot query work fine in SQL but not in Hana.

Kindly suggest some solution.

regards,

PankajK

[kothandaraman_nagarajan]

Hi,

Did you receive any error message if run my latest query in HANA?

Please refer this document for HANA

http://scn.sap.com/docs/DOC-48357

Thanks & Regards,

Nagarajan

[former_member614487]

Thanks Mr. Nagarajan,

                                       I already gone through that document,But there nothing mentioned about pivot query.

However After running ur new query I am getting this error "sql syntax error: incorrect syntax near "Pivot": line 9 col 1"

regards,

PankajK

[kothandaraman_nagarajan]

Hi,

Please check with SAP support or modified your query to get required result without using pivot function.

Thanks & Regards,

Nagarajan