Create Dynamic SQL Where clause in BI Publisher 12c

Question

How can I modify SQL dynamically in BI Publisher 12c based on user's choice?

I have a requirement where I need to apply multiple BIP parameters. Now if user pass something in parameter, it makes sense to apply it but if user let 'All' values to pass through a prompt it make SQL very slow.

Here is an example of how we are using a parameter in Data Model-

SELECT DEPTNO FROM DEPT WHERE 1=1 AND IN DEPTNO IN (: p_deptno)

Now I need to remove AND IN DEPTNO IN (: p_deptno) section when user chooses 'All' to see all the data (removal is a must). Imagine I have many filters and I need to remove all of them when not selected.

I have seen some help for old BIP in here but nothing for new 12 version (per below link: https://only4techies.wordpress.com/2009/12/12/how-to-use-conditional-sql-statements-in-bi-publisher/)

I am using Oracle for my database.

Answer 1

Creating dynamic SQL need little trick and multiple steps. Main idea is to generate a sql based on inputs dynamically. If user selects any parameter(s) then only the 'WHERE' be active else it will be inactive.

This performs efficiently when you have complex SQL with large tables and you want to avoid IN('All') kind of default clauses. This is a three step process, explained below.

Works only for Oracle DB.

Step A. create LOVs -

1. Concat -

   SELECT ''''||ABCD||'''' FROM (
        SELECT LISTAGG(  COLUMN_VALUE,''',''') WITHIN GROUP (ORDER BY 1) as ABCD
   FROM TABLE(sys.ODCIVARCHAR2LIST(:prmTerritory)))
   

2. Break -

   Select ' AND 1=1' from dual where :prmTerritoryConcat=''''''  OR   :prmTerritoryConcat = '''All'''
   union all 
   select '  AND  orig_table.Territory IN ('||  :prmTerritoryConcat ||')'  from dual where :prmTerritoryConcat  <>  '''''' AND  :prmTerritoryConcat <> '''All'''
   

3. Normal LOV for Territory -

   Select Territory from master_Territory

Step B.0 Create below parameters - please note how we are using these parameters in SQL. Display Parameters-

4. parTerritory - set to #3

Hidden Parameters-

5. parTerritoryBreak - set to #2
6. parTerritoryConcat - set to #1

Step B.1 Add all these parameters to Source Database into a procedure.

Step C. create the SQL using above parameters -

6. Query:

   SELECT Territory 
   from original_table original_table 
   WHERE 
   &parTerritoryBreak
   

Test cases - I considered two cases below. Check the output log for the sql BIP is creating. Log should show like this for both cases - Case 1 - when territory isn't selected -

SELECT Territory 
from original_table original_table 
WHERE 
1=1

Case 2 - when territories are selected -

SELECT Territory 
from original_table original_table 
WHERE 1=1 
AND orig_table.Territory IN ('Americas', 'EMEA', 'APAC')