Data based on Conditions in a table

[rindia]

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:

Output of information View:

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

                   

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:

Data Validation:

Now lets run the Calculation view for different conditions.

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

   

Output of CV and data from EMPLOYEE table is matching.

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

  

Output of CV and data from EMPLOYEE table is matching.

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

   

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

Thank you

Raj Kumar Salla