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.
- A User can create any number of conditions.
- A Condition can be based on any number of attributes (Business unit, Gender, etc).
- Each Attribute in a Condition can have only one Operator. (In / Not in).
- 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:
- 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.
- Check the given input parameter is valid or not.
- 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.
- Check how many attributes are included in condition name, if none then skip the processing else move the count to a variable.
- Check the operator whether it is IN or NOT IN for different Attributes in a Condition.
- If the Operator is IN then query should be based on IN operator (EX: GENDER IN ‘Male’).
- If the Operator is NOT IN then query should be based on NOT IN operator (EX: GENDER NOT IN ‘Male’).
- 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
No comments:
Post a Comment