SAP HANA: Handling Dynamic Select Column List and Multiple values in input parameter
Hello Folks,
Its been a long time since i blogged. Hope you are all doing good and enjoying SAP HANA and its exciting innovations.
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.
@Prashant, Thanks for your help in finding “Replace” function as a work around to enable multiple values being passed for filtering.
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.
Interested to see on how you can achieve the same thing i.e “Multiple Values” using Graphical Calculation View, Do have a look on the below blog:
Using Multiple Values in Input parameter for filtering in Graphical Calculation View
Hope you have enjoyed the blog. Let me know your thoughts on the same.
Your’s
Krishna Tangudu
Interesting - why didn't you use Analytic Privileges?
Hi John,
Could not get your question.
Analytic Privileges are used for row level authorizations right?
What i was doing in the above blog is the flexibility to choose "multiple values" in the filter.
You may mention that we can use "Variables" for it. I forgot to add that note.. we were using HTML5 Web Dashboards and hence we were not able to consume variables and we need to use Input parameters in the procedure for filtering of data
So to choose Multiple Values we used the above mentioned approach.
Regards,
Krishna Tangudu
Hi Krishna
Thanks for the nice blog. I tried to use your code to read data from Analytical view instead of a table and getting error message that this function is not allowed in OLAP.
Any suggestions?
Thanks
Ashok
AP's would give the flexibility for row level security, ie only authorized rows - but I don't think we can get the similar function for dynamic columns that he is showing here. In an AP, we can show only the authorized regions, but not selectively show columns depending on the user.
This is of course I am missing something 🙂
Regards,
Justin
Awesome blog Krishna..Very use full..
Regards,
Mahesh
Hi Mahesh,
Thank you so much for your kind words 🙂 .
Regards,
Krishna Tangudu
Nice document Krishna
Regards,
Vivek
Thanks Vivek 🙂
Very Useful document Krishna.
Thank you Sayan for your feedback 🙂
Good one Krishna 🙂
Thanks Raj 🙂
Hi Krishna,
I don't understand the statement REGION_FILTER := ' AND 1=1';
You said that is used, If no Region values are sent as input parameters then it will result in null value hence using a default true condition.
'My question is, how can call this sproc without any input parameter for Region. won't it throw any error?
i.e.Call EMPLOYEE_DETAILS(1);
Or
If I can understand like. Call EMPLOYEE_DETAILS(1, ''); then still control has to go through the below block
IF (REGION <> '%')
THEN SELECT ''''|| REPLACE (:VAR_REGION, ',', ''',''')||'''' INTO REGION_FILTER FROM DUMMY ;
REGION_FILTER:= ' WHERE REGION IN ('||REGION_FILTER||')';
END IF;
isn't it?
If so, how "AND:1=1" will be useful.
Could you please explain?
Thanks,
Sree
Hi Sreedhar,
The Idea is , when nothing is passed it should still behave like passing '%'. So When no value is passed to Region, Then it should not filter any records from the result set.
Thanks for pointing. Have corrected the IF Condition and also one more change is the default value of REGION is 'WHERE 1 = 1' . If you have any more additional input parameters they can be defaulted to 'AND 1=1'.
Regards,
Krishna Tangudu
Hi Krishna,
I just have a small question. Can same functionality be achieved through Graphical approach also? I can see in semantics we have option to hide but dont know how to put the conditions based on parameters?
Thanks and Regards
Sumeet
Hi Sumeet,
I tried the same but was not able to use the "hide" option in semantics and as well as don't think HANA provides column level security.
Regards,
Krishna Tangudu
Hi Krishna,
When I am trying to call the procedure. Its giving me below error.
Could not execute 'call EMPLOYEE_DETAILS1 (4,'EMEA')' in 122 ms 36 µs .
SAP DBTech JDBC: [2048]: column store error: search table error: [2620] "Sumeet"."EMPLOYEE_DETAILS1": line 13 col 122 (at pos 553): [105] (range 1) NullConversion exception
BR
Sumeet
Hi,
There is an away to call this procedure from another procedure and get the result into a table variable?
Something like this:
Thanks
Do you mean:
call EMPLOYEE_DETAILS(1,'AMR,APAC',:t_call);
Yeah but don't work in this case, because EMPLOYEE_DETAILS don't have an OUT argument.
Hi Italo,
have you found a solution to this problem? I am very interested to hear it.
Best,
Maximilian
Hi,
Not yet.
Hi Italo,
I have now circumvented the problem by using temporary tables like in the following example:
CREATE LOCAL TEMPORARY COLUMN TABLE "#temp" LIKE T_DOCUMENT;
EXECUTE IMMEDIATE 'INSERT INTO "#temp" SELECT document_id, text FROM ' || :table_id || ' WHERE document_id = ''' || document_id || '''';
text = SELECT text FROM "#temp";
DROP TABLE "#temp";
I realize that this is not optimal, but I found no other solution. Maybe this works in your case too.
Cheers.
Has anyone found a solution to this problem?
How to get the result of a dynamic query, whether it EXEC/EXEC IMMEDIATE, to a table variable/scalar variable without using temporary tables?
Hi sir,
i am a beginner to SAP HANA. can you please tell me why do you use CS_INT next to integer in the sql statement while creating the table
Hi Krishna ,
I was not able to understand your code, Could you please help me to clear my doubts.
In the first part the below code ,did you use any parameter like input and output .
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 1000
Hi Krishna,
I have a string formed for dynamic sql with where clause having null check. I am getting "InternalError: dberror($.hdb.Connection.executeProcedure): 256 - SQL error, server error code: 256. sql processing error: [256] "<schema>"."<procedure>": line 71 col 5 (at pos 3056): [105] (range 1) NullConversion exception at ptime/query/plan_executor/trex_wrapper/trex_wrapper_body/trex_query.cc:1337"
How to check NULL?
Thanks,
Rashmi