Core SAP HANA SQL script concepts- SELECT with SQL WHERE conditions
Welcome to the second installment of this SAP HANA SQL Scripts core concepts section where we try to understand the SQL WHERE condition and different ways of using it.
SQL WHERE – Because we only take what we need
Most times when we do analysis, we never need all of the data from a table. We usually need a particular sub-set of this data and when we pick up only the part we need, this saves a lot of time in query execution and speeds up performance. These filters are applied using the SQL WHERE clause. After your table name in the SELECT statement, add WHERE clause to the end with the condition(s) for the filter. For example, below we pick up all the age and gender values for all employees whose age is greater than 25 years. This means that this data would be returned for ages 26 or more.
Also note that the number 25 is written without any double or single quotes. This is because in EMP_MASTER, I declared it’s datatype as INTEGER and not as one of the character datatypes.
On pressing execute you can see that 28 rows have been retrieved and on skimming the data, you realize that the filter was successfully applied.
If you wanted to include employees with age 25, just add an equals sign in front of the greater than sign to make it a greater than or equal to condition as shown below.
On execution, you get the data for ages 25 or more as seen below. One of the 25-year-old employee’s records have been highlighted for reference.
AND / OR Operator
Now there might be multiple filters required rather than one. For example, if we extend the above case by saying that I now need the list of Age and Genders from EMP_MASTER for all male employees who are 25 or more in age. To do this, in the SQL WHERE condition, just add an AND keyword in front of the first condition and specify the next one. You can keep adding AND conditions and continue adding more filters like this. I now add the gender filter as male.
Notice that the value has been enclosed in single quotes. This is really important. Whenever the value is a string of characters, it should be in single quotes. Always remember that fields, and object names are always in double quotes but data values that are characters will always be in single quotes.
On executing this. We see no data. So this can either mean that there is no row in this table that matches this condition or that we messed up the filter somehow. In our case, it’s the latter. I screwed this up to teach you an important lesson – Always check the data before applying filters. If you see the data from this table in one of the initial screenshots, you will realize that the filter we need is ‘Male’ and not ’male’. Anything inside a quote is case sensitive.
Now we correct the filter and capitalise the M in male.
Executing it this time provides the correct results.
The below example shows addition of a third filter.
Upon execution you get the result as below.
Now let’s say we need a result where the output should show employees who are 25 or above in age and male with no children or people of age 19. This statement can be a bit misleading and not understood in the way it was meant to. Read it once again. What it means is we need the data for people who are 19 without any further filters or anyone greater than or equal to 25 years but this time with the filter of being male and without children.
In this case, we add an OR condition at the end of the previous SQL WHERE conditions.
On execution, we have the below result. As seen here, due to the OR condition, we now get even records for Female gender who are 19 years of age where even one of them has kids. Similarly, there are now males of 19 in the data with kids. But on everyone at and over 25, the further filters got applied correctly.
Now what if we wished for the gender and children filters to be applied on the 19 year old as well? Well it’s quite simple. In basic mathematics, we learnt that brackets are always evaluated first. The same rule applies here. We put the age restrictions inside a bracket with an OR condition so that this becomes a single block and then the AND conditions with gender and children filter make sure we get the correct result.
Upon execution, we see that the result is what we expected.
Lets have a look on the list of operators which can be used with SQL WHERE clause :
| Operator | Description |
| = | Equal |
| <> | Not Equal |
| > | Greater than |
| < | Less than |
| >= | Greater than or equal |
| <= | Less than or equal |
| BETWEEN | Between an inclusive range |
| LIKE | Search for a pattern |
| IN | Returns multiple values from a column |
SAP HANA SQL SCRIPT LIKE Operator
Now there are situations where we need to return the list of data where a record starts with/ends with/contains a particular character or set of characters. This means that whenever you are looking for some sort of a pattern in the data, you should use like.
Wildcard Characters : % and _
SQL wildcards must be used with SQL LIKE operator. % can be filled with any number of characters. For example, using LIKE ‘%am’ means that it will return all data where the string ends with am. The data output may be ‘shyam’ ‘xysfasdaam’ or just ‘am’
_ has to be filled with exactly one character. For example using LIKE ‘_hyam’ can return data where string ends with hyam. So the output can be shyam , xhyam, yhyam.
These can be used at start of the string, end of it and in between as well. You can use it as LIKE ‘s__%’.This one tells the SAP HANA system that the string we are looking for should be at least of length 3 and should start with a lowercase s. Also can have more characters at the end due to the % wild card So, the result can be shyam, s12, s56testchar and so on.
So using this knowledge, let’s try to find all records from EMP_MASTER where the employee’s phone number starts with the number 6. Since the length of this phone number is irrelevant, we can use a % wildcard as shown below.
The execution result is as below.
Now let’s say our requirement says that the phone numbers we need to look for are exactly 11 characters in length and start with 2 whilst ending with 8. In this case of fixed length, we would need to write the query as below using 9 underscores between 2 and 8.
On execution, we see the records as expected in the output.
SAP HANA SQL Script NOT condition
The NOT is an interesting keyword. You can put it in front of any keyword in the WHERE condition and make it behave opposite to its regular behavior. We apply the NOT as shown below to the previous statement.
As seen below, the two records we got earlier are now excluded and we now have 48 records where the phone number doesn’t start with 2 and doesn’t end with 8.
In the next tutorials, we will learn about new calculated fields and aliases in SAP HANA SQL script. Be sure to check it out.
Help this website grow by sharing this document on social media by using the icons below. Be sure to subscribe to our newsletter when the message pops up for latest alerts on new tutorials.
<<Previous Tutorial Next Tutorial>>