Oracle Analytics and AI

Balaji Pk291989

Summary

OBIP sql query with Bind variable for where conditional statement

Content

Hi All,

  We are converting Oracle Reports to Oracle® Business Intelligence Publisher 12c (12.2.1)  Reports

  in the reports query we have bind variables in "where" and "And" conditions dynamically by calling function "AfterPForm" where they set value to the Bind variables based on the scenario.

  I am facing problem when creating this same . Please help me to attain this functionality in OBIP.

  Below mentioned the sample query and the function condition.

/***********************SQL Query********************************/

select ename ,empno,sal from emp

&P_Where

&P_And

/************************AfterPForm Function******************/

function AfterPForm return boolean is
begin
      select sysdate into v_sysdate
      from dual;
      select to_char(sysdate,'DD.MM.YYYY') into v_sysdate_ch
      from dual;
      :P_SPE_LANG := substr(:P_LANG,1,1); 
      if (:P_Sort is null and :P_MENT is null)  THEN
                        :P_WHERE := ' and deptno is not null';
      end if;
       if (:P_Sort ='E' and :P_MENT=10)  THEN
                        :P_AND := ' and deptno=10';
      end if;
     
                        EXCEPTION
                        when no_data_found  THEN
                        null;
                        end;
    return (TRUE);   
end;

/******************************/

Awaiting for your Reply

Thanks & Regards

Balaji

Thomas Dodds

select ename ,empno,sal

from emp

where deptno = nvl(: P_MENT, deptno)  <-- will return all deptno if var is NULL

AND ( (firstname||' '||lastname)= NVL(: P_fullname,(firstname||' '||lastname))   <-- use a LOV with sql as SELECT  (firstname||' '||lastname) fullname FROM emp ORDER BY 1

if you need multi-select departments then:

select ename ,empno,sal

from emp

where (LEAST(: P_MENT) IS NULL OR (deptno IN : P_MENT)) <-- set multi select on and ALL passes in NULL

AND ( (firstname||' '||lastname)= NVL(: P_fullname,(firstname||' '||lastname))   <-- use a LOV with sql as SELECT  (firstname||' '||lastname) fullname FROM emp ORDER BY 1