SAP HANA Calculation view using SQL script – Part 2
Welcome to the second part of the tutorial introducing SAP HANA Calculation view using SQL script . If you’ve landed directly on this page, I recommend starting from the first tutorial for better understanding. Now, as per our original requirement, we would need a view consisting of a join between VBAK and VBAP. This tutorial aims at achieving this requirement.
SAP HANA Calculation view using SQL script- Table Aliases
To start off, let’s use a table alias to our existing code. We give the alias “VK” to VBAK (you can use any name). Thus, a field MANDT could also be called VK.MANDT which tells the system that the field MANDT comes from table VK (which is the alias for VBAK).
As you might note, there’s only one table and it makes no sense to use an alias. It’s just redundant. Well, we will include VBAP in the next few steps and this was just to show you how the code evolves step by step in slow motion.
SAP HANA Calculation view using SQL script- Adding columns
Again, before we proceed further, let’s add the fields we need from VBAP table to the output list. To do this, right click on the “Columns” folder and click on “Edit Columns”.
This brings up our friendly window which already has the fields we used from VBAK.
In a familiar fashion as earlier, click on the blue icon to Add more columns.
Again, type in the table name which is VBAP in our case. Select the one in schema ECC_DATA.
This brings up all the fields of VBAP. We select a few fields from here too. Press Add to send them to the output.
Once the “Add” button is pressed, the fields selected move to the target section. Press the Finish button to continue. Be careful not to check the “Replace existing columns in output” checkbox. This would remove the existing fields before adding the new ones which is not what we want here.
As seen below, the new fields have been added below the ones we already had. Again, I would like to remind you that what we did just now was just to copy the field names and their datatype details from table VBAP. These steps did not establish any kind of link to the table VBAP whatsoever.
Press OK to add these columns to our scripted node.
AS seen on the right side, you now have the new fields in the “Column” folder. Notice that the icons in front of the new fields are different. As soon as you activate/validate the view, HANA would give them the standard blue icons if they are dimensions (master data) or the orange bars (transaction data).
At this point, if you try to activate this view, it would fail. This is due to the fact that the variable var_out has now 3 new fields which are not defined in the output select statement.
Now, proceeding with our requirement, which was to join tables VBAK and VBAP, we already have the code for output of the required VBAK fields. Also, we added the alias for table VBAK and modified the field names to accommodate this alias.
SAP HANA Calculation view using SQL script- Joining the second table
Next, specify the type of join and the table you are joining it with.
In this case, we use a left outer join to the VBAP table under the ECC_DATA schema. Also, we give the table an alias VP. The new lines of code added are marked in red below.
Next, let’s select the fields we need from VBAP table and add them to the output list of the SELECT statement in the same order as specified in the “Columns” folder.
Next, we need to specify how these tables are being joined. In other words, we specify the join condition or the relationship between these tables. Most tables you receive from SAP Application sources are cross-client and contain the field MANDT. This field should always be involved in the join if both tables have it. To understand the concept of what cross-client means, it’s explained in one of the sections in a separate tutorial which you can access by clicking here.
The ON keyword marks the start of a join condition. VK.MANDT = VP.MANDT tells the system that we need to join the tables based on rows in VBAK to rows in VBAP where the MANDT values match. Since we used an alias, the table names were not needed to be written.
As explained, this join condition should always be the first one to be applied whenever the sources are SAP tables containing this field.
Add the next condition using the AND operator. The sales document number (VBELN) is the primary key connecting both the tables. Add that condition as shown below and that would complete your join condition. Make sure you have a semicolon at the end. Save and activate this SAP HANA Scripted Calculation View.
Execute a data preview and check the raw data tab. You would see that the join has resulted in a singular view of the data set as we expected.
This concludes our tutorial on joining two tables via SAP HANA Calculation view using SQL script.
Make sure to share this content on social media using the share buttons below to show your support to this website and to keep it alive.