Skip to Content


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.


Screen Shot 2013-12-17 at 4.26.05 PM.png


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.


Screen Shot 2013-12-17 at 4.26.51 PM.png


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





To report this post you need to login first.

27 Comments

You must be Logged on to comment or reply to a post.

    1. Krishna Tangudu Post author

      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

      (0) 
      1. Ashokkumar Narasimhan

        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

        (0) 
    2. Justin Molenaur

      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

      (0) 
  1. sreedhara sameer kumar ilapavuluri

    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

    (0) 
    1. Krishna Tangudu Post author

      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

      (0) 
  2. sumeet durgia

    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

    (0) 
    1. Krishna Tangudu Post author

      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

      (0) 
      1. sumeet durgia

        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

        (0) 
  3. Italo N.

    Hi,

    There is an away to call this procedure from another procedure and get the result into a table variable?

      Something like this:

    t_call = call EMPLOYEE_DETAILS(1,’AMR,APAC’);

    Thanks

    (0) 
            1. Maximilian Grundke

              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.

              (0) 
              1. Aswath Gopinath

                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?

                (0) 
  4. surya patro

    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

    (0) 
  5. Rashmi Nagendra

    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

    (0) 

Leave a Reply