Welcome to the second part of this SAP HANA SQL Scripts Basics section, where we try to understand the SQL WHERE condition and the different ways to use it.
SQL WHERE - Because we only take what we need
Most of the time, when we analyze, we never need all the data in a table. We usually need a specific subset of this data, and getting only the part we need saves a lot of time executing queries and speeds up performance. These filters are applied using the SQL WHERE clause. After your table name in the SELECT statement, add the WHERE clause at the end with the conditions for the filter. Below, for example, we collect all the age and gender values of all employees whose age is older than 25 years. This means that this data will be returned for people over 26 years of age.
Also note that the number 25 is written without single or double quotes. This is because in EMP_MASTER I declared its data type as INTEGER and not one of the character data types.
When you press run, you see that 28 rows have been retrieved, and when you browse through the data, you see that the filter was applied successfully.
If you want to include employees 25 years of age, simply add an equal sign in front of the greater than sign to make the condition greater than or equal to as shown below.
On the run, you get data 25 years or older, as shown below. One of the 25-year employee's records has been highlighted for reference.
AND/OR Operator
Now multiple filters may be required instead of one. For example, if we extend the previous case by saying that now I need the list of ages and genders of EMP_MASTER for allhe-Employees 25 years of age or older. To do this, in the SQL WHERE condition, simply add an AND keyword in front of the first condition and specify the next one. You can continue to add AND conditions and continue to add more filters like this. Now I add the gender filter as male.
Note that the value is enclosed in single quotes. This is really important. When the value is a string, it must be enclosed in single quotes. Always remember that field and object names are always enclosed in double quotes, but data values that are characters are always enclosed in single quotes.
By doing this. We don't see any data. So this could mean that there is no row in this table that matches this condition, or that we have somehow broken the filter. In our case, it is the latter. I screwed it up to teach you an important lesson: Always check your data before applying filters. If you look at the data for this table in one of the initial screenshots, you'll notice that the filter we need is 'Male' and not 'Male'. Everything in a quote is case sensitive.
Now we correct the filter and write M in masculine.
Running it this time gives the correct results.
The following example shows the addition of a third filter.
On execution you will get the result as below.
Now let's say we need a result where the output should show employees who are 25 or older, and males without children, or people who are 19 years old. This statement can be a bit misleading and not be understood in the way it was intended. Read it one more time. What this means is that we need the data for people who are 19 years old without additional filters or anyone older than or equal to 25 years old, but this time with the filter of being male and without children.
In this case, we add an OR condition to the end of the above SQL WHERE conditions.
When executed, we have the following output. As seen here, due to the OR condition, we now get equal records for females who are 19 years old, where even one of them has children. Consequently, there are now 19-year-old men in the data with children. But in everyone over the age of 25, the additional filters were applied correctly.
What if we wanted the gender and child filters to apply to the 19-year-old as well? Well, it's quite simple. In basic math, we learned that parentheses are always evaluated first. The same rule applies here. We bracket the age constraints with an OR condition so it becomes a single block, and then AND conditions with the gender and child filter ensure we get the correct result.
In the execution, we see that the result is what we expected.
Let's see the list of operators that can be used with the SQL WHERE clause:
| Operator | Description |
| = | Fair |
| <> | It is not the same |
| > | Better than |
| < | Less than |
| >= | greater than or equal to |
| <= | less than or equal to |
| IN BETWEEN | Between an inclusive area |
| AS | look for a pattern |
| I | Return multiple values from a column |
Operador SAP HANA SQL SCRIPT LIKE
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 when looking for some kind of pattern in the data, use like.
Wildcards :% and _
SQL wildcards must be used with the SQL LIKE operator.% can be filled with any number of characters. For example, using LIKE '%am' means that it returns all data where the string ends with am. Data output can be 'shy'es' 'xysfasdaes'or only'es'
_must be completed with exactly one character. For example, using LIKE '_hyam' can return data where the string ends with hyam. Then the output can be pHyam, XHyam, yHyam.
These can also be used at the beginning of the chain, at the end of the chain, and in the middle. You can use it as LIKE 's__%'. This tells the SAP HANA system that the string we are looking for must be at least 3 in length and must start with lowercase s. It may also have more characters at the end due to the % wildcard, so the result may beshyam, s12, s56testchar and so on.
So, using this knowledge, let's try to find all EMP_MASTER records 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 result of the execution is the following.
Now let's say our requirement says that the phone numbers we need to search for are exactly 11 characters long and start with 2 and end with 8. In this fixed-length case, we write the query as below with 9 underscores between 2 and 8.
At execution, we see the inputs as expected at the output.
SAP HANA SQL Script NO condition
NOT is an interesting keyword. You can put it in front of any keyword in the WHERE condition and have it behave opposite of its normal behavior. We do NOT apply as below to the above statement.
As seen below, the two records we got earlier are now excluded and we now have 48 records where the phone number does not start with 2 or end with 8.
In upcoming tutorials, we will learn about new calculated fields and aliases in SAP HANA SQL script. Make sure to check it out.
Help this website grow by sharing this document on social media using the icons below. Be sure to sign up for our newsletter when the message pops up to get the latest alerts on new tutorials.
<