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.

SAP HANA SQL SCRIPT SQL CASE STATEMENT NESTED SELECT

Below is the result of execution. After employee ID 1005, you can see the CATEGORY switch to value DELTA as per our logic.

SAP HANA SQL SCRIPT SQL CASE STATEMENT NESTED SELECT

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.

SAP HANA SQL SCRIPT SQL CASE STATEMENT NESTED SELECT

The below execution confirms that the code works well.

SAP HANA SQL SCRIPT SQL CASE STATEMENT NESTED SELECT

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.

SAP HANA SQL SCRIPT SQL CASE STATEMENT NESTED SELECT

On execution, the below result set is produced.

SAP HANA SQL SCRIPT SQL CASE STATEMENT NESTED SELECT

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.

<<Previous Tutorial                                                                                                 Next Tutorial>>

Tagged , , , , , , , , , , , , , .

10 Comments

    • 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

  1. 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;

  2. 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;

  3. Hi,

    How to use CASE statement in WHERE condition of SELECT query.

    Thank you for sharing the wounderful website for learning SAP HANA.

  4. 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.

Leave a Reply

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