Создайте динамическое выражение SQL Where в BI Publisher 12c

Question

Как я могу динамически изменять SQL в BI Publisher 12c по выбору пользователя?

У меня есть требование, где мне нужно применить несколько параметров BIP. Теперь, если пользователь передает что-то в параметре, имеет смысл применить это, но если пользователь пропустит значения "All" через приглашение, это сделает SQL очень медленным.

Вот пример того, как мы используем параметр в Data Model-

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

Теперь мне нужно удалить AND IN DEPTNO IN (: p_deptno) раздел, когда пользователь выбирает "Все", чтобы увидеть все данные (удаление обязательно). Представьте, что у меня много фильтров, и мне нужно удалить их все, когда они не выбраны.

Я видел некоторую помощь для старого BIP здесь, но ничего для новой версии 12 (по ссылке ниже: https://only4techies.wordpress.com/2009/12/12/how-to-use-conditional-sql-statements-in-bi-publisher/)

Я использую Oracle для своей базы данных.

Answer 1

Создание динамического SQL требует небольшого трюка и нескольких шагов. Основная идея - динамически генерировать sql на основе входных данных. Если пользователь выбирает какой-либо параметр (ы), тогда будет активным только "ГДЕ", иначе он будет неактивным.

Это эффективно, когда у вас есть сложный SQL с большими таблицами и вы хотите избежать IN('All')вид условий по умолчанию. Это трехэтапный процесс, описанный ниже.

Работает только для БД Oracle.

Шаг A. Создание LOV -

1. Конкат -

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

2. Перемена -

   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. Нормальный LOV для территории -

   Выберите территорию из master_Territory

Шаг B.0 Создайте параметры ниже - обратите внимание, как мы используем эти параметры в SQL. Параметры дисплея

4. parTerritory - установлен на #3

Скрытые параметры-

5. parTerritoryBreak - установлен на #2
6. parTerritoryConcat - установлен на #1

Шаг B.1 Добавьте все эти параметры в исходную базу данных в процедуру.

Шаг C. создайте SQL, используя вышеуказанные параметры -

6. Запрос:

   SELECT Territory 
   from original_table original_table 
   WHERE 
   &parTerritoryBreak
   

Тестовые случаи - я рассмотрел два случая ниже. Проверьте выходной журнал для sql BIP. Журнал должен выглядеть так для обоих случаев - Случай 1 - когда территория не выбрана -

SELECT Territory 
from original_table original_table 
WHERE 
1=1

Случай 2 - при выборе территорий -

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