Core SAP HANA SQL script concepts- Calculated fields and Alias
Welcome to the third instalment of this SAP HANA SQL Scripts core concepts section where we try to understand how to create new calculated fields and how to rename field names using SQL alias concept.
Now let’s play with transaction data for a while. Let’s see how data in our EMP_SALARY table looks like.
As seen below, we have 50 records – one for each employee ID showing their salary as an integer value.
Let’s do an on the fly calculation of a new value – bonus which is 20% of the base salary. To do this, just add a bracket (optional in this case but good for clarity) and put in the formula as salary multiplied by 0.2
As you see below, we got the correct results. But the field name is also displayed as the formula we used for calculating the bonus. This should rather have a more meaningful name ideally.
SQL ALIAS for fields
To solve the above mentioned problem, we use something known as Alias. An alias is an alternate name given to something in SQL script. Just add the AS keyword after the formula and provide the alias field name in double quotes. In our example, we use BONUS as the new field name.
Execute this script and you would see that the new field name has now appeared instead of the formula which is much better in terms of presentation.
You can even create new fields with constant values and give them a meaningful alias.
For example, I create a new field called OTHER_BENEFITS with a constant value of 10 for each employee.
On execution, you can see that the alias and the values now appear. In this way, you can create new fields that are not present in the base table.
SQL alias for table names
We already learnt about field aliases where we rename individual fields or formulas as some other name that we desire. Similarly, we can create table aliases where we give the table name some other (usually shorter) name so that it’s easier to type it in when used again and again in the same statement. This also increases code readability.
For example, to select employee ID and first name from EMP_NAMES table, we can provide an alias by placing the alias (ename in this case) in front of the table name separated by a blank space. Then you can also write EMP_ID field name as ename. EMP_ID which tells the system that you need to pick up the EMP_ID field from ename table which is an alias of EMP_NAMES table. It makes no sense to do this when there is only one table in the select statement since the system knows it needs to get this field from the only available table but when you have two or more, it is always better to use aliases since if you have the same field in multiple tables, you can specify using aliases where to pick it from.
The execution provides the same result as if there were no alias.
I hope this was easy to understand and follow. Stay tuned for the next part where we learn aggregation functions and further clauses for WHERE conditions.
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.