Predicates and Predicate functions in HANA

#Bizoneness:

Over the last few newsletters, we reviewed functions in HANA and their SQL equivalents. Our last in this series is predicates to filter your data.

I'll be referring to both the functions and types and the aggregate functionsnewsletters, so you may want to review those before reading this one.

Before I discuss the predicate functions, let's discuss what a predicate is. Predicates are Boolean expressions. They return a value of true or false. In some cases, they may have a third expression indicating no answer, returning NULL or nildepending on the database or language used. Predicates are the key for searches of a given set of data.

The basic Boolean operators we discussed of equal, not equal, less than, greater than, etc., are good examples.

Suppose I have the following query of sales orders:

SELECT t0."DocDate",t0."DocTotal"  FROM ORDR T0
        

That would give you thousands of rows of data.

I could use aggregate functions to group the data:

 SELECT
   T0."DocDate",
   COUNT(T0."DocTotal") AS "Order Count" ,
   SUM(T0."DocTotal") AS "Daily Sales"  
FROM ORDR T0 
GROUP BY T0."DocDate"
        

Which drops the rows to around 900. I could use other grouping mechanisms to filter more, but I can use predicates to filter the results in the WHERE clause. I could look at only data for 2015. I'd include

WHERE YEAR(T0."DocDate") = 2015
        

like this

SELECT
   T0."DocDate",
   COUNT(T0."DocTotal") AS "Order Count" ,
   SUM(T0."DocTotal") AS "Daily Sales"  
FROM ORDR T0 
WHERE YEAR(T0."DocDate") = 2015
GROUP BY T0."DocDate"
ORDER BY T0."DocDate"
        

As we learned, the logical Boolean operators could help us define the predicate even more. For example, if I wanted sales orders in the first six months of 2015 with greater than $100,000 in sales, the where clause could be

WHERE 
       YEAR(T0."DocDate") = 2015
   AND MONTH(T0."DocDate") <= 6
   AND T0."DocTotal" >= 100000
        

Besides filtering, we can use predicates to create relationships. Any Join works on its predicate being true after the ON part of the clause. For example, this query adds Items sold to the query.

SELECT
   T0."DocDate",
   COUNT(T0."DocTotal") AS "Order Count" ,
   SUM(T0."DocTotal") AS "Daily Sales",  
   COUNT(T1."ItemCode") AS "Item Count",
   T1."ItemCode", 
     T1."Description"
FROM 
   ORDR T0 
   INNER JOIN "RDR1" T1 ON T0."DocEntry" = T1."DocEntry"

WHERE 
       YEAR(T0."DocDate") = 2015
   AND MONTH(T0."DocDate") <= 6
   AND T0."DocTotal" >= 100000
GROUP BY T0."DocDate",T1."ItemCode", T1."Dscription"
ORDER BY T0."DocDate"
        

gives us the items sold on each $100,000 day.

One more use of predicate is in the CASE expression, which determines what is displayed. Here, for example, I list the status of active Production orders.

SELECT 
   T0."DocNum", 
CASE T0."Status"
      WHEN 'P' THEN 'Planned'
      WHEN 'R' THEN 'Released'
      ELSE 'Inactive'
    END AS "Status"
FROM OWOR T0 
        

Using Boolean expressions, predicates filter data for us or determine if a relationship between rows of tables exists.

Predicate expressions.

We've used Boolean expressions so far to define a predicate. There are also several Predicate expressions you can use for predicates.

NULL

Generally, we expect every column in every row to have data, but that's not always the case. Changes in how we refer to data, the inclusion of user-defined fields, and users skipping fields are three situations where data might not be present.

For those cases, we use IS NULL, and more frequently, to assure data existence IS NOT NULL.

Here's an example of an unused field

SELECT "Uom"  FROM OWOR T0 WHERE "Uom" IS NOT NULL
        

returns no rows on my system. Uom is based on OITM. This is an unused column on my system, probably from an older version of SAP B1.

Here's one where the comment is optional and so may or may not be there:

SELECT "Comments"  FROM OWOR T0 WHERE "Comments" IS NOT NULL
        

IN

Sometimes, you want more than one value for the same column to be true. For example, you can do something like this for active production orders only

SELECT 
   T0."DocNum", 
CASE T0."Status"
      WHEN 'P' THEN 'Planned'
      WHEN 'R' THEN 'Released'
      ELSE 'Inactive'
    END AS "Status"
FROM OWOR T0 
WHERE 
      T0."Status" = 'P' 
   OR T0."Status" = 'R'
        

IN combines the compound predicate into a single one, listing all the true cases for testing

T0."Status" IN ('P','R')
        

Our Production order query becomes

SELECT 
   T0."DocNum", 
CASE T0."Status"
      WHEN 'P' THEN 'Planned'
      WHEN 'R' THEN 'Released'
      ELSE 'Inactive'
    END AS "Status"
FROM OWOR T0 WHERE 
      T0."Status" IN ('P','R')
        

LIKE

The OEC inventory codes by part type. For example, all labor inventory items start with an L. If I wanted to find all labor items, I'd like to see all those L-prefixed items.

LIKE uses strings and wildcard characters to determine the true cases. an _determines a single character and a % is for any length of characters.

If I had data I was searching for of Pizza, all of these would return true:

• 'Piz_a' - Any character in the fourth position
• 'P%' - Any string beginning with P
• '%za' - Any string ending with za
• '%zz%' -Any string with zz

I could write a query like this looking for labor codes, excluding LeMon printers, which begin with LM:

 SELECT 
   T0."ItemCode",
   T0."ItemName" 
FROM OITM T0
WHERE
       T0."ItemCode" LIKE 'L%' 
   AND T0."ItemCode"  NOT LIKE '_M%'
        

BETWEEN

You may want to look at a range, often a date. For example All production orders in the next 3 months. For HANA, that would be.

SELECT 
   T0."DocNum", 
   T0."PostDate" 
FROM OWOR T0 
WHERE 
       t0."PostDate" >= TO_DATE(NOW()) 
   AND t0."PostDate" <= ADD_Months(TO_DATE(NOW()),3) 
        

Between compresses it to a range comparison.

SELECT 
   T0."DocNum", 
   T0."PostDate" 
FROM OWOR T0 
WHERE 
T0."Postdate" BETWEEN TO_Date(Now()) AND ADD_MONTHS(TO_DATE(NOW()),3)
        

The first part of the range must be less than the second part. Otherwise, the predicate will return false.

One word on BETWEEN and SQL. While it is part of Transact SQL, Apparently it is not part of SAP B1's implementation. I've tried to get it to work with no success, and fall back on the SQL version of the compound boolean expression. If your queries will be transposed between HANA and SQL, you may want to use

       t0."PostDate" >= GETDATE() 
   AND t0."PostDate" <= DATEADD(Month,GETDATE(),3) 
        

instead of the BETWEEN predicate for faster conversion.

Predicates provide the filters to explain your data by focusing on the important parts, explaining cryptic data in human-readable terms, and joining it to other data sets. Once you understand its logic, it can be a powerful tool in building HANA.