In this document we will be discussing on how to handle multiple values passed in to filter condition using “REPLACE” Function in procedure and also Dynamic SQL using Execute Immediate.
Note: We followed this approach since our reporting tool is HTML5 web dashboards and we cannot consume Variables with "Multiple Values" for the same.
A) Problem Description:
--> How to handle multiple values in filter condition ?
Example:
If we have “Region” as a filtering criterion and we have 3 Regions namely: AMR, APAC and EMEA and the user want the flexibility to select the list of regions for which he wants to analyze the data.
We can use “Replace” Function to split the multiple values coming from input
--> Handling Dynamic select column list in the output ?
Example:
If we have a field like “Employee Type” as criteria on which the Output columns selected is depended up on as shown below:
a) If “Employee Type” = 1 then the user should be able to see Column1, Column2, Column3
b) If “Employee Type” = 2 then the user should be able to see Column1, Column3, Column4
c) If “Employee Type” = 3 then the user should be able to see Column 2,Column3, Column4
We can use “Execute Immediate” for the above-mentioned example to retrieve the data.
Will be explaining the above mentioned 2 functionalities in detailed using the below example:
B) Preparing Data:
First, Let us create a table named “EMPLOYEE” with the following DDL and insert some test records for our example as shown below:
/* Creating a table named Employee */
CREATE COLUMN TABLE EMPLOYEE" ("EMP NO" INTEGER CS_INT,
"EMPLOYEE NAME" VARCHAR(200), "EMPLOYEE TYPE" INTEGER CS_INT, "GENDER" VARCHAR(10),
"AGE" INTEGER CS_INT,
"REGION" VARCHAR(10), "SALARY" DECIMAL(18,
0) CS_FIXED)
/* Insert Statements */
/*Employee Type = 1: Cricket Players */
Insert into “EMPLOYEE" values (1,'Sachin',1,'M',40,'APAC',50000);
Insert into “EMPLOYEE" values (2,'Ganguly',1,'M',42,'APAC',40000);
Insert into “EMPLOYEE" values (3,'Dravid',1,'M',40,'AMER',40000);
Insert into "EMPLOYEE" values (4,'Laxman',1,'M',43,'AMER',40000);
Insert into "EMPLOYEE" values (5,'Dhoni',1,'M',35,'EMEA',40000);
Insert into “EMPLOYEE" values (6,'Sehwag',1,'M',36,'EMEA',30000);
Insert into "EMPLOYEE" values (7,'Kohli',1,'M',23,'EMEA',20000);
Insert into "EMPLOYEE" values (8,'Kumar',1,'M',22,'EMEA',10000);
/*Employee Type = 2: Tekken Players */
Insert into “EMPLOYEE" values (1,'Law',2,'M',24,'APAC',30000);
Insert into “EMPLOYEE" values (2,'Eddie',2,'M',26,'EMEA',150000);
Insert into “EMPLOYEE" values (3,'Paul',2,'M',23,'APAC',120000);
Insert into “EMPLOYEE" values (4,'Howrang',2,'M',22,'AMER',60000);
Insert into “EMPLOYEE" values (5,'Xiayou',2,'F',22,'AMER',8000);
Insert into “EMPLOYEE" values (6,'Nina',2,'F',22,'AMER',70000);
/*Employee Type = 3: Tennis Players */
Insert into “EMPLOYEE" values (1,'Federer',3,'M',30,'APAC',1150000);
Insert into “EMPLOYEE" values (2,'Nadal',3,'M',29,'APAC',5230000);
Insert into “EMPLOYEE" values (3,'Djokovic',3,'29',24,'APAC',5045000);
Insert into “EMPLOYEE" values (4,'Murray',3,'M',24,'APAC',55650000);
Insert into “EMPLOYEE" values (5,'Sampras',3,'M',44,'AMER',5660000);
Insert into “EMPLOYEE" values (6,'Agassi',3,'M',45,'AMER',5056000);
Insert into “EMPLOYEE" values (7,'Venus',3,'F',28,'AMER',9500500);
Insert into “EMPLOYEE" values (8,'Serena',3,'F',29,'AMER',9507000);
/*Employee Type = 4: Football Players */
Insert into “EMPLOYEE" values (1,'Messi',4,'M',24,'APAC',510000);
Insert into “EMPLOYEE" values (2,'Ronaldo',4,'M',28,'AMER',500);
Insert into “EMPLOYEE" values (3,'Xavi',4,'M',30,'EMEA',5002300);
Insert into “EMPLOYEE" values (4,'Beckham',4,'M',40,'EMEA',7850000);
Now we have the data in the “EMPLOYEE” table. Which has data for 3 Regions and 4 Types of employees.
C) Our Scenario:
1) Below are the conditions on Output column list based on “Employee Type” Selected by the user:
a) If “Employee Type” = 1 then the user should be able to see EMP NO, EMPLOYEE NAME, GENDER, AGE, REGION, SALARY
b) If “Employee Type” = 2 then the user should be able to see EMP NO, EMPLOYEE NAME, AGE, REGION, SALARY
c) If “Employee Type” = 3 then the user should be able to see EMP NO, EMPLOYEE NAME, GENDER, REGION, SALARY
d) If “Employee Type” = 4 then the user should be able to see EMP NO, EMPLOYEE NAME, GENDER, AGE, REGION
2) Also we will enable the option of selecting “Region” list of his choice while analyzing the data to the user:
a) AMR
b) APAC
c) EMEA
Let us create a procedure named EMPLOYEE_DETAILS and see how we can achieve this:
CREATE PROCEDURE EMPLOYEE_DETAILS
EMPLOYEE_TYPE VARCHAR(5),
REGION VARCHAR(10)) LANGUAGE SQLSCRIPT AS
BEGIN
DECLARE VAR_REGION VARCHAR(10000); DECLARE SQL_STR VARCHAR(3000); DECLARE VAR_EMPTYPE INTEGER; DECLARE REGION_FILTER VARCHAR(10000);
DECLARE SQLERRORS CONDITION FOR SQL_ERROR_CODE 10001;
/* Declaring the exception handler to log the SQL query which resulted in SQL errors */
DECLARE EXIT HANDLER FOR sqlexception
BEGIN
SQL_STR := 'SQL Error Exception. The Query Executed is: ' || SQL_STR || ' The Error Message is: '|| ::SQL_ERROR_MESSAGE; SIGNAL SQLERRORS SET MESSAGE_TEXT = SQL_STR;
/* To get the query in the output message itself */
END;
VAR_EMPTYPE := EMPLOYEE_TYPE; VAR_REGION := REGION;
REGION_FILTER := ' WHERE 1=1'; /* If no Region values are sent as input parameters then it will pass '' value hence using a default true condition */
/* Forming Region Filter Condition Using REPLACE Function */
IF (REGION <> '%' AND REGION != '' )
THEN SELECT ''''|| REPLACE (:VAR_REGION, ',', ''',''')||'''' INTO REGION_FILTER FROM DUMMY ;
REGION_FILTER:= ' WHERE REGION IN ('||REGION_FILTER||')';
END IF
;
-----FORMING EMPLOYEE TYPE CONDITION-----------
IF (VAR_EMPTYPE = 1) THEN
SQL_STR := 'SELECT "EMP NO", "EMPLOYEE NAME", "GENDER", "AGE", "REGION", "SALARY" FROM "EMPLOYEE" '||REGION_FILTER||' AND "EMPLOYEE TYPE" = 1';
/* to dynamically form the Select statement */
EXECUTE IMMEDIATE (:SQL_STR);
/* Executing the string using Execute Immediate */
ELSEIF (VAR_EMPTYPE = 2)
THEN
SQL_STR := 'SELECT "EMP NO", "EMPLOYEE NAME", "AGE", "REGION", "SALARY" FROM "EMPLOYEE" '||REGION_FILTER||' AND "EMPLOYEE TYPE" = 2';
/* to dynamically form the Select statement */
EXECUTE IMMEDIATE (:SQL_STR);
/* Executing the string using Execute Immediate */
ELSEIF (VAR_EMPTYPE = 3)
THEN
SQL_STR := 'SELECT "EMP NO", "EMPLOYEE NAME", "GENDER", "REGION", "SALARY" FROM "EMPLOYEE" '||REGION_FILTER||' AND "EMPLOYEE TYPE" = 3';
/* to dynamically form the Select statement */
EXECUTE IMMEDIATE (:SQL_STR);
/* Executing the string using Execute Immediate */
ELSE
SQL_STR := 'SELECT "EMP NO", "EMPLOYEE NAME", "GENDER", "AGE", "REGION" FROM "EMPLOYEE" '||REGION_FILTER||' AND "EMPLOYEE TYPE" = 4';
/* to dynamically form the Select statement */
EXECUTE IMMEDIATE (:SQL_STR);
/* Executing the string using Execute Immediate */
END IF ;
END ;
D) Checking the output:
Now let us see the output if it is working fine as desired.
Case 1: If the user wants to get the details for Employee Type = 1 i.e. cricketers in Regions: AMR, APAC.
Note: We followed this approach since our reporting tool is HTML5 web dashboards and we cannot consume Variables with "Multiple Values" for the same.
A) Problem Description:
--> How to handle multiple values in filter condition ?
Example:
If we have “Region” as a filtering criterion and we have 3 Regions namely: AMR, APAC and EMEA and the user want the flexibility to select the list of regions for which he wants to analyze the data.
We can use “Replace” Function to split the multiple values coming from input
--> Handling Dynamic select column list in the output ?
Example:
If we have a field like “Employee Type” as criteria on which the Output columns selected is depended up on as shown below:
a) If “Employee Type” = 1 then the user should be able to see Column1, Column2, Column3
b) If “Employee Type” = 2 then the user should be able to see Column1, Column3, Column4
c) If “Employee Type” = 3 then the user should be able to see Column 2,Column3, Column4
We can use “Execute Immediate” for the above-mentioned example to retrieve the data.
Will be explaining the above mentioned 2 functionalities in detailed using the below example:
B) Preparing Data:
First, Let us create a table named “EMPLOYEE” with the following DDL and insert some test records for our example as shown below:
/* Creating a table named Employee */
CREATE COLUMN TABLE EMPLOYEE" ("EMP NO" INTEGER CS_INT,
"EMPLOYEE NAME" VARCHAR(200), "EMPLOYEE TYPE" INTEGER CS_INT, "GENDER" VARCHAR(10),
"AGE" INTEGER CS_INT,
"REGION" VARCHAR(10), "SALARY" DECIMAL(18,
0) CS_FIXED)
/* Insert Statements */
/*Employee Type = 1: Cricket Players */
Insert into “EMPLOYEE" values (1,'Sachin',1,'M',40,'APAC',50000);
Insert into “EMPLOYEE" values (2,'Ganguly',1,'M',42,'APAC',40000);
Insert into “EMPLOYEE" values (3,'Dravid',1,'M',40,'AMER',40000);
Insert into "EMPLOYEE" values (4,'Laxman',1,'M',43,'AMER',40000);
Insert into "EMPLOYEE" values (5,'Dhoni',1,'M',35,'EMEA',40000);
Insert into “EMPLOYEE" values (6,'Sehwag',1,'M',36,'EMEA',30000);
Insert into "EMPLOYEE" values (7,'Kohli',1,'M',23,'EMEA',20000);
Insert into "EMPLOYEE" values (8,'Kumar',1,'M',22,'EMEA',10000);
/*Employee Type = 2: Tekken Players */
Insert into “EMPLOYEE" values (1,'Law',2,'M',24,'APAC',30000);
Insert into “EMPLOYEE" values (2,'Eddie',2,'M',26,'EMEA',150000);
Insert into “EMPLOYEE" values (3,'Paul',2,'M',23,'APAC',120000);
Insert into “EMPLOYEE" values (4,'Howrang',2,'M',22,'AMER',60000);
Insert into “EMPLOYEE" values (5,'Xiayou',2,'F',22,'AMER',8000);
Insert into “EMPLOYEE" values (6,'Nina',2,'F',22,'AMER',70000);
/*Employee Type = 3: Tennis Players */
Insert into “EMPLOYEE" values (1,'Federer',3,'M',30,'APAC',1150000);
Insert into “EMPLOYEE" values (2,'Nadal',3,'M',29,'APAC',5230000);
Insert into “EMPLOYEE" values (3,'Djokovic',3,'29',24,'APAC',5045000);
Insert into “EMPLOYEE" values (4,'Murray',3,'M',24,'APAC',55650000);
Insert into “EMPLOYEE" values (5,'Sampras',3,'M',44,'AMER',5660000);
Insert into “EMPLOYEE" values (6,'Agassi',3,'M',45,'AMER',5056000);
Insert into “EMPLOYEE" values (7,'Venus',3,'F',28,'AMER',9500500);
Insert into “EMPLOYEE" values (8,'Serena',3,'F',29,'AMER',9507000);
/*Employee Type = 4: Football Players */
Insert into “EMPLOYEE" values (1,'Messi',4,'M',24,'APAC',510000);
Insert into “EMPLOYEE" values (2,'Ronaldo',4,'M',28,'AMER',500);
Insert into “EMPLOYEE" values (3,'Xavi',4,'M',30,'EMEA',5002300);
Insert into “EMPLOYEE" values (4,'Beckham',4,'M',40,'EMEA',7850000);
Now we have the data in the “EMPLOYEE” table. Which has data for 3 Regions and 4 Types of employees.
C) Our Scenario:
1) Below are the conditions on Output column list based on “Employee Type” Selected by the user:
a) If “Employee Type” = 1 then the user should be able to see EMP NO, EMPLOYEE NAME, GENDER, AGE, REGION, SALARY
b) If “Employee Type” = 2 then the user should be able to see EMP NO, EMPLOYEE NAME, AGE, REGION, SALARY
c) If “Employee Type” = 3 then the user should be able to see EMP NO, EMPLOYEE NAME, GENDER, REGION, SALARY
d) If “Employee Type” = 4 then the user should be able to see EMP NO, EMPLOYEE NAME, GENDER, AGE, REGION
2) Also we will enable the option of selecting “Region” list of his choice while analyzing the data to the user:
a) AMR
b) APAC
c) EMEA
Let us create a procedure named EMPLOYEE_DETAILS and see how we can achieve this:
CREATE PROCEDURE EMPLOYEE_DETAILS
============================================
-- Description : Procedure for Explaining dynamic sql using execute immediate and Replace function
============================================
(EMPLOYEE_TYPE VARCHAR(5),
REGION VARCHAR(10)) LANGUAGE SQLSCRIPT AS
BEGIN
DECLARE VAR_REGION VARCHAR(10000); DECLARE SQL_STR VARCHAR(3000); DECLARE VAR_EMPTYPE INTEGER; DECLARE REGION_FILTER VARCHAR(10000);
DECLARE SQLERRORS CONDITION FOR SQL_ERROR_CODE 10001;
/* Declaring the exception handler to log the SQL query which resulted in SQL errors */
DECLARE EXIT HANDLER FOR sqlexception
BEGIN
SQL_STR := 'SQL Error Exception. The Query Executed is: ' || SQL_STR || ' The Error Message is: '|| ::SQL_ERROR_MESSAGE; SIGNAL SQLERRORS SET MESSAGE_TEXT = SQL_STR;
/* To get the query in the output message itself */
END;
VAR_EMPTYPE := EMPLOYEE_TYPE; VAR_REGION := REGION;
REGION_FILTER := ' WHERE 1=1'; /* If no Region values are sent as input parameters then it will pass '' value hence using a default true condition */
/* Forming Region Filter Condition Using REPLACE Function */
IF (REGION <> '%' AND REGION != '' )
THEN SELECT ''''|| REPLACE (:VAR_REGION, ',', ''',''')||'''' INTO REGION_FILTER FROM DUMMY ;
REGION_FILTER:= ' WHERE REGION IN ('||REGION_FILTER||')';
END IF
;
-----FORMING EMPLOYEE TYPE CONDITION-----------
IF (VAR_EMPTYPE = 1) THEN
SQL_STR := 'SELECT "EMP NO", "EMPLOYEE NAME", "GENDER", "AGE", "REGION", "SALARY" FROM "EMPLOYEE" '||REGION_FILTER||' AND "EMPLOYEE TYPE" = 1';
/* to dynamically form the Select statement */
EXECUTE IMMEDIATE (:SQL_STR);
/* Executing the string using Execute Immediate */
ELSEIF (VAR_EMPTYPE = 2)
THEN
SQL_STR := 'SELECT "EMP NO", "EMPLOYEE NAME", "AGE", "REGION", "SALARY" FROM "EMPLOYEE" '||REGION_FILTER||' AND "EMPLOYEE TYPE" = 2';
/* to dynamically form the Select statement */
EXECUTE IMMEDIATE (:SQL_STR);
/* Executing the string using Execute Immediate */
ELSEIF (VAR_EMPTYPE = 3)
THEN
SQL_STR := 'SELECT "EMP NO", "EMPLOYEE NAME", "GENDER", "REGION", "SALARY" FROM "EMPLOYEE" '||REGION_FILTER||' AND "EMPLOYEE TYPE" = 3';
/* to dynamically form the Select statement */
EXECUTE IMMEDIATE (:SQL_STR);
/* Executing the string using Execute Immediate */
ELSE
SQL_STR := 'SELECT "EMP NO", "EMPLOYEE NAME", "GENDER", "AGE", "REGION" FROM "EMPLOYEE" '||REGION_FILTER||' AND "EMPLOYEE TYPE" = 4';
/* to dynamically form the Select statement */
EXECUTE IMMEDIATE (:SQL_STR);
/* Executing the string using Execute Immediate */
END IF ;
END ;
D) Checking the output:
Now let us see the output if it is working fine as desired.
Case 1: If the user wants to get the details for Employee Type = 1 i.e. cricketers in Regions: AMR, APAC.
As shown above only AMR, APAC data is shown and only the desired column list for Employee type = 1 i.e. EMP NO, EMPLOYEE NAME, GENDER, AGE, REGION, SALARY is shown.
Case 2: If the user wants to get the details for Employee Type = 4 i.e. Football players in Regions: EMEA.
As shown above only EMEA data is shown and only the desired column list for Employee type = 1 i.e. EMP NO, EMPLOYEE NAME, GENDER, AGE, REGION is shown.
Source: scn.sap.com
No comments:
Post a Comment