Skip to Content
0

Join column name where column name contains '.' character - SAP HANA

Jul 13, 2017 at 10:15 PM

88

avatar image

I am trying to create a script-based calculation view. The script is as follows:

/********* Begin Procedure Script ************/ 
BEGIN 
   var_out = SELECT TOP 10 PD.PRODUCTID, PD.CATEGORY, 
             sum(SOI.GROSSAMOUNT) as REVENUE, SOI.CURRENCY  as CURRENCY_CODE
     FROM "SAP_HANA_DEMO"."sap.hana.democontent.epm.data::MD.Products" as PD
   inner join ("SAP_HANA_DEMO"."sap.hana.democontent.epm.data::SO.Header" as   SOH left outer join
 "SAP_HANA_DEMO"."sap.hana.democontent.epm.data::SO.Item" as SOI 
  on SOH.SALESORDERID = SOI.SALESORDERID)
  on PD.PRODUCTID = "SOI.PRODUCT.PRODUCTID"
  where to_date("SOH.HISTORY.CREATEDAT") > add_years(current_date,-1*:NUMBER_OF_YEARS)
  group by PD.PRODUCTID, PD.CATEGORY, CURRENCY_CODE
  order by REVENUE desc; 


END /********* End Procedure Script ************/

But while activating getting below error:

Error while activating /skPackage1/CV_SCRIPT.calculationview:[skPackage1:CV_SCRIPT.calculationview] Repository: Encountered an error in repository runtime extension;Model inconsistency. Deploy Calculation View: SQL: invalid column name: SOI.PRODUCT.PRODUCTID: line 7 col 21 (at pos 722)

It is evident that it is not accepting a column name with '.' included in the column name itself. I am using demo table "SAP_HANA_DEMO"."sap.hana.democontent.epm.data::SO.Item" here from SAP standard schema where indeed there column name is PRODUCT.PRODUCTID.

How should I use this colun name in SQL syntax for join?

Regards,

Sumit Kundu

soitem.jpg (135.2 kB)
10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

1 Answer

Best Answer
Florian Pfeffer
Jul 14, 2017 at 04:03 AM
0

You did enclose the table alias name in the the double quotes which should include the column name. So the alias name for the table is interpreted as column name part.

Change "SOI.PRODUCT.PRODUCTID" to SOI."PRODUCT.PRODUCTID".

The same is necessary for "SOH.HISTORY.CREATEDAT" (-> SOH."HISTORY.CREATEDAT").

Regards,
Florian

Show 1 Share
10 |10000 characters needed characters left characters exceeded

Thanks a lot Florian, that was perfect. The calculation view was activated successfully.

0