Skip to Content

In this document we will see how to fetch data based on the conditions available in a table. Typically we will see such requirements when applications are built based on SAP HANA.

         This document is prepared based on version SPS 11 Revision 112                           


All the user conditions are available in the Table: USER_CONDITIONS and these conditions will be applied on the Table: EMPLOYEE. To keep it simple, let us consider two tables with sample data as shown below:


Employee.jpg


Condition.jpg

Output of information View:

Output.jpg

All the dimensions and measures in the view are available in table EMPLOYEE and HEADCOUNT is the aggregation of EMP_ACTIVE. The final output is the data with conditions from the table applied.


  1. A User can create any number of conditions.
  2. A Condition can be based on any number of attributes (Business unit, Gender, etc).
  3. Each Attribute in a Condition can have only one Operator. (In / Not in).
  4. Different Attributes in a Condition can have different Operators (In, Not in, etc).


In order to apply conditions in the table USER_CONDITIONS, we need user and condition name, which will be the Input parameters to the view. The user who uses the application or runs the Calculation view can be determined using SESSION_USER. Hence input parameter for the user is not required, and only the condition_name is required.


Input parameter to view:

  1. IP_CONDITION_NAME (based on conditions set by user which are saved in table: USER_CONDITIONS)


Approach:


The complexity here is in using the OPEARTOR (IN / NOT IN) for the attributes in a Condition. For such scenarios, SQLScript Calculation Views can serve the purpose easily.


  1. Check the given input parameter is valid or not.
  2. Check whether the given condition name exists in condition table or not , if not then skip the processing else move the count to a variable.
  3. Check how many attributes are included in condition name, if none then skip the processing else move the count to a variable.
  4. Check the operator whether it is IN or NOT IN for different Attributes in a Condition.
  5. If the Operator is IN then query should be based on IN operator (EX: GENDER IN ‘Male’).
  6. If the Operator is NOT IN then query should be based on NOT IN operator (EX: GENDER NOT IN ‘Male’).
  7. Declare all the used variables.

Let us write the sqlscript code  based on above steps:


1. Check the given input parameter is valid or not.

IF (:IP_CONDITION_NAME IS NOT NULL AND :IP_CONDITION_NAME <> ”) THEN

…….

ELSE

…….

END IF;

2. Check whether the given condition name exists in condition table or not , if not then skip the processing else move the count to a variable.

     SELECT COUNT(CONDITION_NAME) INTO VAR_COUNT

        FROM RSALLA.USER_CONDITIONS

          WHERE CONDITION_NAME = :IP_CONDITION_NAME;

       IF VAR_COUNT > 0 THEN

        ….

       ELSE

        …..

       END IF;

3. Check the existence of all the attributes for given Condition name.

         SELECT COUNT(*) INTO COUNT_BU FROM RSALLA.USER_CONDITIONS

          WHERE USER = SESSION_USER AND CONDITION_NAME = :IP_CONDITION_NAME

             AND ATTRIBUTE = ‘BUSINESS_UNIT’;

4. Check the operator whether it is IN or NOT IN for different Attributes in a Condition.

                         

IF :COUNT_BU > 0 THEN

   SELECT TOP 1 CASE WHEN OPERATOR = ‘IN’ THEN ‘I’

              WHEN OPERATOR = ‘NOT IN’ THEN ‘N’

              ELSE ”

              END INTO FLAG_OPERATOR_BU

       FROM RSALLA.USER_CONDITIONS

        WHERE USER = SESSION_USER

AND CONDITION_NAME = :IP_CONDITION_NAME

AND ATTRIBUTE = ‘BUSINESS_UNIT’;

             END IF;

5. If the Operator is IN then query should be based on IN operator (EX: BUSINESS_UNIT IN ‘0001’).

SELECT FISCAL_YEAR, GENDER, BUSINESS_UNIT, SUM(EMP_ACTIVE) AS HEADCOUNT

FROM RSALLA.EMPLOYEE

WHERE    :COUNT_BU = 0 OR

( :FLAG_OPERATOR_BU = ‘I’ AND BUSINESS_UNIT IN

                         (SELECT DISTINCT BUSINESS_UNIT FROM RSALLA.USER_CONDITIONS

WHERE USER = SESSION_USER AND CONDITION_NAME = :IP_CONDITION_NAME AND ATTRIBUTE = ‘BUSINESS_UNIT’) )


If there is no condition on attribute Business Unit then COUNT_BU = 0 will be true and the rest will be false. No condition will be applied on Business Unit.


If condition exists then COUNT_BU = 0 will be false and if the operator is IN then Flag of operator will be ‘I’ and IN operator will be applied on business Unit.


As each Attribute in a Condition can have only one Operator ( IN/NOT IN), Query is written in such a way that When IN is true, NOT IN becomes false.

6. If the Operator is NOT IN then query should be based on NOT IN operator (EX: BUSINESS_UNIT NOT IN ‘0001’).

( :FLAG_OPERATOR_BU = ‘N’ AND (BUSINESS_UNIT NOT IN

         (SELECT DISTINCT BUSINESS_UNIT FROM RSALLA.USER_CONDITIONS

           WHERE USER = SESSION_USER AND CONDITION_NAME = :IP_CONDITION_NAME AND ATTRIBUTE = ‘BUSINESS_UNIT’)

   OR BUSINESS_UNIT IS NULL ))

      

If the operator is NOT IN then Flag of operator will be ‘N’ and NOT IN operator will be applied on business Unit.


If you observe properly, for NOT IN operator, there is extra piece of code

OR BUSINESS_UNIT IS NULL.

This is required as HANA will ignore NULL values for NOT IN operator. Below is the example for GENDER values for null, blank values

                    Null.jpg

7. Declare all the used variables.

DECLARE VAR_COUNT, COUNT_BU, COUNT_GENDER              SMALLINT DEFAULT 0;

DECLARE FLAG_OPERATOR_BU, FLAG_OPERATOR_GENDER  VARCHAR (1) DEFAULT ”;


Now we will put all pieces of code together and the final script is:


BEGIN

       DECLARE VAR_COUNT, COUNT_BU, COUNT_GENDER       SMALLINT DEFAULT 0;

       DECLARE FLAG_OPERATOR_BU, FLAG_OPERATOR_GENDER  VARCHAR (1) DEFAULT ”;

    IF (:IP_CONDITION_NAME IS NOT NULL AND :IP_CONDITION_NAME <> ”) THEN

      SELECT COUNT(CONDITION_NAME) INTO VAR_COUNT

        FROM RSALLA.USER_CONDITIONS

          WHERE USER = SESSION_USER AND CONDITION_NAME = :IP_CONDITION_NAME;

       IF VAR_COUNT > 0 THEN

         SELECT COUNT(*) INTO COUNT_BU FROM RSALLA.USER_CONDITIONS

          WHERE USER = SESSION_USER AND CONDITION_NAME = :IP_CONDITION_NAME

           AND ATTRIBUTE = ‘BUSINESS_UNIT’;

               IF :COUNT_BU > 0 THEN

                SELECT TOP 1 CASE WHEN OPERATOR = ‘IN’ THEN ‘I’

                                   WHEN OPERATOR = ‘NOT IN’ THEN ‘N’

                                    ELSE ”

                                     END INTO FLAG_OPERATOR_BU

                     FROM RSALLA.USER_CONDITIONS

                      WHERE USER = SESSION_USER

                       AND CONDITION_NAME = :IP_CONDITION_NAME

                        AND ATTRIBUTE = ‘BUSINESS_UNIT’;

               END IF;

              

               SELECT COUNT(*) INTO COUNT_GENDER FROM RSALLA.USER_CONDITIONS

                WHERE USER = SESSION_USER AND CONDITION_NAME = :IP_CONDITION_NAME

                 AND ATTRIBUTE = ‘GENDER’;

               IF :COUNT_GENDER > 0 THEN

                SELECT TOP 1 CASE WHEN OPERATOR = ‘IN’ THEN ‘I’

                                   WHEN OPERATOR = ‘NOT IN’ THEN ‘N’

                                    ELSE ”

                                      END INTO FLAG_OPERATOR_GENDER

                     FROM RSALLA.USER_CONDITIONS

                      WHERE USER = SESSION_USER

                       AND CONDITION_NAME = :IP_CONDITION_NAME

                        AND ATTRIBUTE = ‘GENDER’;

                       

               END IF;

              

               TAB_RESULT =

               SELECT FISCAL_YEAR, GENDER, BUSINESS_UNIT, SUM(EMP_ACTIVE) AS HEADCOUNT

                FROM RSALLA.EMPLOYEE

                 WHERE

                  (:COUNT_BU = 0 OR

                        ( :FLAG_OPERATOR_BU = ‘I’ AND BUSINESS_UNIT IN

                         (SELECT DISTINCT VALUE AS BUSINESS_UNIT

                           FROM RSALLA.USER_CONDITIONS

                            WHERE USER = SESSION_USER

                             AND CONDITION_NAME = :IP_CONDITION_NAME

                              AND ATTRIBUTE = ‘BUSINESS_UNIT’) )

                                 OR

                             ( :FLAG_OPERATOR_BU = ‘N’ AND (BUSINESS_UNIT NOT IN

                              (SELECT DISTINCT VALUE AS BUSINESS_UNIT

                                FROM RSALLA.USER_CONDITIONS

                                 WHERE USER = SESSION_USER

                                  AND CONDITION_NAME = :IP_CONDITION_NAME

                                   AND ATTRIBUTE = ‘BUSINESS_UNIT’)

                                    OR BUSINESS_UNIT IS NULL ))      

                   )

                    AND

                     (:COUNT_GENDER = 0 OR

                           ( :FLAG_OPERATOR_GENDER = ‘I’ AND GENDER IN

                            (SELECT DISTINCT VALUE AS GENDER

                              FROM RSALLA.USER_CONDITIONS

                               WHERE USER = SESSION_USER

                                AND CONDITION_NAME = :IP_CONDITION_NAME

                                 AND ATTRIBUTE = ‘GENDER’) )

                                        OR

                             ( :FLAG_OPERATOR_GENDER = ‘N’ AND (GENDER NOT IN

                              (SELECT DISTINCT VALUE AS GENDER

                                FROM RSALLA.USER_CONDITIONS

                                 WHERE USER = SESSION_USER

                                  AND CONDITION_NAME = :IP_CONDITION_NAME

                                   AND ATTRIBUTE = ‘GENDER’)

                                    OR GENDER IS NULL ))      

                     )

                   GROUP BY FISCAL_YEAR, GENDER, BUSINESS_UNIT

                ;

       ELSE

         TAB_RESULT = SELECT ” AS FISCAL_YEAR, ” AS GENDER, ” AS BUSINESS_UNIT,

                       0 AS HEADCOUNT

                        FROM DUMMY;

       END IF;

    ELSE

      TAB_RESULT = SELECT ” AS FISCAL_YEAR, ” AS GENDER, ” AS BUSINESS_UNIT,

                    0 AS HEADCOUNT

                     FROM DUMMY;

    END IF;

      

       VAR_OUT = SELECT FISCAL_YEAR, GENDER, BUSINESS_UNIT,

                  SUM(HEADCOUNT) AS HEADCOUNT

                   FROM :TAB_RESULT

                    GROUP BY FISCAL_YEAR, GENDER, BUSINESS_UNIT;

                                 

END;

Input Parameter:

InputParamter.jpg

Data Validation:

Now lets run the Calculation view for different conditions.

Test case 1: User – RSALLA, Condition name – CONDITION_1

    Condition_01.jpg

Output of CV and data from EMPLOYEE table is matching.

Test case 2: User – RSALLA, Condition name – CONDITION_2

   Condition_02.jpg

Output of CV and data from EMPLOYEE table is matching.


Test case 3: User – RSALLA, Condition name – CONDITION_5 (does not exist)

    Condition_05.jpg

Output of CV is just a single row with default values. This can be modified as per the requirement.



Thank you

Raj Kumar Salla

To report this post you need to login first.

11 Comments

You must be Logged on to comment or reply to a post.

  1. Lars Breddemann

    Hmm… not quite sure I really get what this is about.

    There are predefined filters that should be applied to queries on top of specific information models depending on the user who’s running them, right?

    While I generally think this would be something better managed by the application, I am not sure this is the best approach here.

    What is wrong with e.g. just storing all the conditions that should be applied in the WHERE clause in one string in the table? Upon execution you simply fetch the WHERE clause, plug it into APPLY_FILTER() and off you go.

    That way you don’t need to decrypt the conditions stored separately in the and fiddle them back together again.

    Other than that I like the way the document is prepared and how the write up is done. 🙂

    (0) 
    1. Raj K Post author

      Hi Lars,

      Noted.

      Basically it is designed before that the Post service will populate the data in Condition table in the way I described before. Due to that I used this logic.

      I got your point  in storing all the conditions in a string where we can use APPLY_FILTER.

      Now’s its too late to change the design but I can implement to check this in Sandbox, atleast.

      Thank you for your valuable feedback 🙂

      (0) 

Leave a Reply