OBIP sql query with Bind variable for where conditional statement

Balaji Pk291989 · Feb 22, 2017 8:00AM

Summary

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 · Feb 22, 2017 8:00AM

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

Venkat Thota - BIP · Feb 22, 2017 8:00AM

Hello Balaji ,

How many parameters does report contains ,please list them here along with input values. Thanks

Balaji Pk291989 · Feb 22, 2017 8:00AM

Hi Thomas,

Thanks for your Reply..

in addition to this i am passing a Bind variable in String.

For example

select ename ,empno,sal from emp

&P_Where(i.e where (firstname||' '||lastname)='Tom King ' );

how can i access the Parameter in the SQL query of a OBIP dataset.

Regards

Balaji.

Thomas Dodds · Feb 22, 2017 8:00AM

Don't do a type for type conversion ...

PS: Your function, as presented, adds no value (and creates a syntax issue for your SQL)

Simply your SQL should be:

select ename ,empno,sal

from emp

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

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

MFA for Oracle Community

Oracle Analytics Cloud and Server

Categories

OBIP sql query with Bind variable for where conditional statement

Summary

Content

Answers