Month: November 2018

In case you need to find out who did a last change or created an object such as a calculation view, you can find out in table “_SYS_REPO”.”OBJECT_HISTORY”:

1
2
3

SELECT * FROM "_SYS_REPO"."OBJECT_HISTORY" WHERE 
package_id LIKE '%myPackage%' AND
object_name LIKE '%myCV%'

The column OBJECT_SUFFIX has ‘calculationview’ for Calculation Views (surprise!), there is VERSION_ID column and then ACTIVATED_AT has the timestamp and ACTIVATED_BY the user that created or modified the object. The discussion topic Creator/Owner of Attribute/Analytical/Calculation views mentions that table.

The function IFNULL Function (Miscellaneous) in HANA allows to replace a null result with the second parameter of the function.
Table TCURC has the different currency codes like AUD, USD, JPY and the second table TCURX has the number of decimal places. This second table is an “exception” table, i.e., only currency codes that do not have 2 decimal places are in this table.

This poses a problem: values stored in transactional tables in JPY (Yen) are “divided” by 100: as the number of decimals is zero, the system stores the value as if divided by 100. So, out of SAP ECC they need to be multiplied by 10 to the power of (2 minus number of decimals). But as TCURX is an “exception” table, before doing this in a table function or other way, we may need to make sure that for every value in TCURC we have a value in TCURX:

1
2
3
4

SELECT A."WAERS", IFNULL(B."CURRDEC",'2') AS "CURRDEC" 
  FROM "myschema"."TCURC" AS A 
  LEFT OUTER JOIN "myschema"."TCURX" AS B
  ON A."WAERS" = B."CURRKEY"

The result now has all currency codes even when they are not in table TCURX.