I am working on a graphical calculation view in HANA Studio. We have a requirement for MATNR column as follows: If the MATNR column is alpha numeric, return the same MATNR as output. If the MATNR column is numeric, then trim the leading zeros from Matnr column. Can you please help how this can be achieved in the Calculated Column using NATIVE SQL functions if possible? Fyi, I have tried creating a custom DB function and a custom Table function for the same. But i am not sure how to import either of these into the calculation view and apply it on MATNR column in the Calculation View itself. Please provide your help on this. the function is something like this:
CREATE FUNCTION SK. FN_CONVERTMATNR(v_in VARCHAR(100)) RETURNS v_out VARCHAR(100) LANGUAGE SQLSCRIPT SQL SECURITY INVOKER AS BEGIN DECLARE EXIT HANDLER FOR SQLEXCEPTION BEGIN v_out := :v_in; END; v_out := to_number(:v_in); v_out := ltrim(:v_in,'0'); END;
Here is the calculation View:
MATNR_TRM is the calculated column for which the custom function need to be applied .