SAP HANA Scripted Calculation View – Part 3

In real time projects, usually this scripted calculation view is the base for building reports. The reporting team can have a hard time understanding these field names. To be honest the HANA development team wouldn’t know the descriptions of most of them too. Before we start, as a general disclaimer, if you’ve landed directly on this page, I recommend starting from the first tutorial for better understanding.

These names are actually all meaningful words in German. For example, the VBELN field that we have been using is short for Vertriebsbelegnummer which of course means Sales document number. For those of us less enlightened in German, we usually proceed to rename these fields using field aliases which we learnt earlier.

The names we use will be derived from their actual descriptions in SAP source system tables.

Do check them out at these links – Click here for VBAK and Click here for VBAP.

Also, in a view, it’s usually good to have the Sales Document number (VBELN) and the Item number (POSNR) shown next to each other. Since they came from different tables in our example, they are a bit far apart right now. Let’s proceed to fix this too along with the naming.

Renaming & Ordering fields in SAP HANA Scripted Calculation view

Below is the code we had already written in the scripted calculation view from the previous steps we performed to join tables VBAK and VBAK. The code has been now organized a bit better now by giving each field a separate line. There is no difference in the functionality. It’s just that the following steps that we do will look neater once there’s enough space next to the field name.

SAP HANA Scripted calculation view SQL Script

As explained earlier, the var_out should have the same structure (name and sequence) of the fields in the “Columns” folder. So let’s first edit the “Columns” to reflect the field names and the order we require. To achieve this, right click on “Columns” and select “Edit Columns”.

SAP HANA Scripted calculation view SQL Script

This opens up our familiar Column list where you can edit the properties you wish to. In each row, remove the field name in the “Name” column and provide a better and meaningful label instead.

SAP HANA Scripted calculation view SQL Script

As seen below, all 9 labels were changed to something which can be much easily read by anyone who uses this view for reporting purposes or any further database modelling. As everything good in life, this good practice comes with a warning.

Try to keep these names consistent.

For example, the field VBELN is a commonly used field. This field is present in many tables and thus would propagate to many views. Each view might rename it to maybe SALES_DOC or DOC_NO or DOCUMENT or oranges or apples or whatever. Bad synchronization between field names in these cases can cause confusions in development and or debugging a problem whenever it arises.

You have been warned.

Next, we would like the ITEM field (renamed from field POSNR) to move next to the SALES_DOC field (Renamed from VBELN). Thus, we need it to move from position 7 to position 3.

To achieve this great feat, click on the ITEM row which you wish to move and then click on the UP arrow which is ironically shown below by my red arrow pointing down.

A single click on this arrow moves the row one position up. My amazing mathematical skills tell me that in our case, we would require 4 clicks to reach the third position.

SAP HANA Scripted calculation view SQL Script

After 4 clicks, ITEM is next to SALES_DOC as we intended. Press OK to confirm the field list.

SAP HANA Scripted calculation view SQL Script

As seen on the right side, the “Columns” folder now displays the correct order and better field names of these fields.

SAP HANA Scripted calculation view SQL Script

Now, add the table alias to each field using the AS keyword as learnt earlier. This would rename each of these fields to the alias name. Ignore the order of the fields in this step.

SAP HANA Scripted calculation view SQL Script

Now, to correct the order, just cut and paste the line containing the POSNR (or the ITEM as per the new alias name) to the position just below the VBELN (or the SALES_DOC as per the new alias).

Thus, the field names and order again match the “Columns” folder and the universe is in complete harmony once again.

Once done rejoicing the moment, save and activate the view.

SAP HANA Scripted calculation view SQL Script
Execute a data preview. As seen below, the data is shown perfectly with easily understandable field names. Also, SALES_DOC is next to ITEM for easier analysis.

SAP HANA Scripted calculation view SQL Script

This finishes the third part of this tutorial series on the introduction to scripted calculation view. Read on to learn more about applying filters, variables and input parameters.

Help this website grow by sharing this document on social media by using the icons below. Be sure to subscribe to our newsletter when the message pops up for latest alerts on new tutorials.

If you feel the website helped you, please also contribute any small amount by using the “Donate button” on the right side of this page to help with the operational costs of this website and other systems involved.

<<Previous Page                                                                                                Next Tutorial>>

 

Tagged .

7 Comments

  1. Hi Shyam,

    In the snapshot above, NET_VALUE and CURRENCY are in sequence in SQL as well as Columns in right pane but in data preview it is not in sequence, why so?

    In my system as well it is the same issue.

    • Hi Anubhav,
      HANA data previews measures at the end of the data preview and the dimensions before that. NET_VALUE is a measure and hence was moved to the end. Data preview is just a place for the developer to test his/her development. The final data output and sequence only matters in the end report. There might be a setting to change this somewhere in HANA studio preferences but its highly irrelevant in my opinion.
      In back end modeling, try to just care about data quality and not the cosmetics of it.

      Shyam

  2. Hi Shyam,

    Can you please look at the below issue.
    When i execute the above scripted calc view with same data as in my tables. I am getting the below error.

    Message :
    Repository: Encountered an error in repository runtime extension;Model inconsistency. Deploy Calculation View: SQL: sql syntax error: incorrect syntax near “on”: line 1 col 194 (at pos 194)
    Set Schema DDL statement: set schema “SYSTEM”
    Type DDL: create type “_SYS_BIC”.”sripack/ZCA_SCRIPTVIEW2/proc/tabletype/VAR_OUT” as table (“MANDT” VARCHAR(3), “VBELN” VARCHAR(10), “VBTYP” VARCHAR(1), “AUART” VARCHAR(4), “NETWR” DECIMAL(15,2), “WAERK” VARCHAR(5), “POSNR” DECIMAL(6,0), “MATNR” VARCHAR(18), “MATWA” VARCHAR(18))
    Procedure DDL: create procedure “_SYS_BIC”.”sripack/ZCA_SCRIPTVIEW2/proc” ( OUT var_out “_SYS_BIC”.”sripack/ZCA_SCRIPTVIEW2/proc/tabletype/VAR_OUT” ) language sqlscript sql security definer reads sql data as on
    /********* Begin Procedure Script ************/
    BEGIN
    var_out = SELECT VK.”MANDT”,VK.”VBELN”,VK.”VBTYP”,
    VK.”AUART”,VK.”NETWR”,VK.”WAERK”,
    VP.”POSNR”,VP.”MATNR”,VP.”MATWA”

    FROM “SRINIVASP”.”ABAP1″ VK
    LEFT OUTER JOIN “SRINIVASP”.”VBAP” VP

    ON VK.”MANDT” = VP.”MANDT”
    AND VK.”VBELN” = VP.”VBELN”

    ;

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

    Please suggest me the correct procedure.

    Thanks,
    Srinivas.P

    • In your code – > sqlscript sql security definer reads sql data as on

      The last “on” is not required. remove that and it should work. Let me know.

  3. Hi i am renaming using in calculation view of sql script view.activating the program i got this error.
    Repository: Encountered an error in repository runtime extension;Model inconsistency. Deploy Calculation View: SQL: sql syntax error: incorrect syntax near “VK”: line 6 col 7 (at pos 298)
    Set Schema DDL statement: set schema “SYSTEM”

Leave a Reply

Your email address will not be published. Required fields are marked *