Core SAP HANA SQL script concepts- SQL CASE & Nested SELECT
Welcome to the eighth installment of this SAP HANA SQL Scripts core concepts section where we learn how to pick up different data for the same field when provided with unique conditions with SQL CASE statement. We will also then understand the concept of having a SELECT statement acting as a filter to other SELECT statement which is also called the NESTED SELECT.
SQL CASE STATEMENT in HANA SQL SELECT
The case statement is very useful and important thing to learn. There are many instances where a new field needs to be calculated but the values may vary with the values of other fields in the row of data. Let’s say we have an example where we need a new field CATEGORY which should be filled with value ALPHA if the employee ID is between 1001 and 1005. Otherwise for all other employee IDs, fill this field with value DELTA.
The case statement has the below syntax.
CASE
WHEN <condition1>
THEN <result1>
WHEN <condition2>
THEN <result2>
WHEN <conditionN>
THEN <resultN>
ELSE <exception_result >
END
As seen above, it should start with keyword CASE and should finish at keyword END.
<condition> stands for the check that must be performed.
<result> stands for the corresponding output for its condition.
<exception result> for all cases that don’t fall under any conditions mentioned in WHEN statement
Using this logic, we build the below select statement for this requirement and give the result an alias of CATEGORY.
Below is the result of execution. After employee ID 1005, you can see the CATEGORY switch to value DELTA as per our logic.
If you have more conditions, feel free to add more WHEN… THEN conditions as per your requirement. In addition to the condition in our example above, if you need a CATEGORY displayed called GAMMA for employee ID between 1006 and 1008, just add another WHEN.. THEN as shown below.
The below execution confirms that the code works well.
SQL NESTED SELECT
Many a times, you come across a requirement which asks you to pick up something from a table based on a list coming in from another table. That is where you can use nested selects to make sure your dataset is filtered accordingly. For example, let’s say you need the list of all employees and their first and last names whose age is greater than 25. Now we know that the first and last names exist in EMP_NAMES table but the age is present in the EMP_MASTER. You can also do the INNER JOIN here but since we don’t need any field from the EMP_MASTER in the output, a JOIN is not required. You can write the SELECT as shown below. The select in brackets executes first producing a list of employee IDs whose age is more than 25. And then this list is fed into the IN filter of the outer SELECT to get the required data.
On execution, the below result set is produced.
In our next and final SQL core concepts Part 9 tutorial, we understand the most important concept – SQL JOIN and SQL UNION. Be sure to check it out.
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.