1 person found this helpful
I have been developing an application with Oracle APEX version 5.1. I'm trying to generate an interactive report based on the filter conditions I created.
Let's assume the information is in Page 1. What I want to achieve is:
if p1_displayoption = 'deptno', the interactive report shows records whose department number is p1_deptno
if p1_displayoption = 'tp', the interactive report shows records whose start date and finish date are between p1_startdate and p1_finishdate (that is where I have problem)
if p1_displayoption = 'all', the interactive report shows all records
I have tried decode and case. The problem is both functions only return one row, however, there will be more than one STARTDATE and FINISHDATE in the table lie between P1_STARTDATE and P1_FINISHDATE.
My code is:
Select deptno, startdate, finishdate......
where deptno = decode(p1_displayoption, 'deptno', p1_deptno,
and startdate = decode(p1_displayoption, 'deptno', startdate,
'tp', -- that's where the problem is. What I want to do is: startdate>=p1_startdate and startdate<=p1_finishdate
and finishdate = decode(p1_displayoption, 'deptno', finishdate,
'tp', -- What I want to do is: finishdate>=p1_startdate and finishdate<=p1_finishdate
I really appreciate the time and help!
Use this approach to ensure that the optimizer can utilise available indexes or partitions:
select deptno , startdate , finishdate , ... from tablename where :p1_option = 'deptno' and deptno = :p1_deptno union all select deptno , startdate , finishdate , ... from tablename where :p1_option = 'tp' and startdate <= to_date(:p1_finishdate, 'yyyy-mm-dd') and coalesce(finishdate, date '9999-12-31') >= to_date(:p1_startdate, 'yyyy-mm-dd') union all select deptno , startdate , finishdate , ... from tablename where :p1_option = 'all'
tablename.finishdatecan be null.