Skip to Content

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

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)
Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

1 Answer

  • Best Answer
    Jul 14, 2017 at 04:03 AM

    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

    Add comment
    10|10000 characters needed characters exceeded