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.
Don’t see any donate button?
Hi Naresh,
There were no donations 🙂 . So I decided to free the space up.
I forgot to remove the paragraph about donations from this page.
Regards,
Shyam
How to use this case statement in calculation views?
In graphical views, use an If() clause instead as – IF(condition,,
Hi
I am not able to understand the error in my below code.
Please help me
select ename.”EMP_ID”,ename.”FIRST_NAME”,emast.”AGE”,esal.”SALARY”, (esal.”SALARY”) * 0.2 AS “BONUS”,
CASE
WHEN esal.”SALARY” >= 3000
THEN ‘MANAGER’
ELSE ‘EMPLOYEE’ AS “DESIGNATION”
from “BWUSER”.”EMP_NAME” ename
left outer join “BWUSER”.”EMP_MASTER_DATA” emast
on
ename.”EMP_ID” = emast.”EMP_ID”
left outer join “BWUSER”.”EMP_SALARY” esal
on
ename.”EMP_ID” = esal.”EMP_ID”
where emast.”AGE” >= 25
order by ename.”EMP_ID” desc;
Sorry for the late reply. Are you still facing the above issue?
Please paste the error message too.
“END” after else condition is missing in case statement… if am not wrong
Hi Krishna, Please try out below query:
select ename.”EMP_ID”,
ename.”FIRST_NAME”,
emast.”AGE”,
esal.”SALARY”,
(esal.”SALARY” * 0.2) AS “BONUS”,
CASE
WHEN esal.”SALARY” >= 3000
THEN ‘MANAGER’
ELSE ‘EMPLOYEE’
END AS “DESIGNATION”
from “BWUSER”.”EMP_NAME” ename
left outer join “BWUSER”.”EMP_MASTER_DATA” emast
on ename.”EMP_ID” = emast.”EMP_ID”
left outer join “BWUSER”.”EMP_SALARY” esal
on ename.”EMP_ID” = esal.”EMP_ID”
where emast.”AGE” >= 25
order by ename.”EMP_ID” desc;
Hi,
How to use CASE statement in WHERE condition of SELECT query.
Thank you for sharing the wounderful website for learning SAP HANA.
Hi,
How to use CASE statement in WHERE condition of SELECT query. Can you share a example code?
Thank you for sharing the wounderful website for learning SAP HANA.
Hello,
My requirement is
case
when c.a1 = 1 then b.1 b.2 b.3 else ‘ ‘ end as b1 b2 b3
but not able to write this in Case,
You must have a lot of pride in writing quality content. I’m impressed with the amount of solid information you have written in your article. I hope to read more.
Great article like this require readers to think as they read. I took my time when going through the points made in this article. I agree with much this information.
I have a doubt regarding using input parameters in a case statement