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.

Please read [Under what circumstances may I add “urgent” or other similar phrases to my question, in order to obtain faster answers?](//meta.stackoverflow.com/q/326569) - the summary is that this is not an ideal way to address volunteers, and is probably counterproductive to obtaining answers. Please refrain from adding this to your questions. — halfer, Sep 19 '18 at 08:14
I found a way to do this. We are using lexical parameters. Process is little complex but made life easier. If anyone wants to know please connect me. — Koushik Roy, Jan 14 '20 at 14:55
You are welcome (and encouraged) to put an answer down below, so if you no longer sign into the platform in the future, readers can still benefit from what you have learned. — halfer, Jan 14 '20 at 14:56

Koushik Roy · Accepted Answer · 2020-02-06T18:07:34.443

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 -

Concat -

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

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'''

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-

parTerritory - set to #3

Hidden Parameters-

parTerritoryBreak - set to #2
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 -

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')

Create Dynamic SQL Where clause in BI Publisher 12c

1 Answers1