Sunday, 22 May 2016

Data based on Conditions in a table

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.

Source: scn.sap.com