How to display description in the prompt and filter by id

There was a question on OTN forum http://forums.oracle.com/forums/thread.jspa?threadID=885967&tstart=15 how to display description in the dashboard prompt by selecting corresponding id and use it in filter. Maybe there is much easier solution then this so if someone knows please let me know.

We will have only one dashboard prompt so there is no need to have two prompts, one for description and one for id and separate GO buttons. This example is based on displaying CALENDAR_YEAR and using CALENDAR_YEAR_ID in filter.

This example only for a test purpose. So I didn't test performance.

Make a dashboard prompt with CALENDAR_YEAR column:



Answers report:



You see this in filter on CALENDAR_YEAR_ID column:

TIMES.CALENDAR_YEAR_ID = EVALUATE('GET_ID_FROM_DESC(%1)' as double, @{pv_desc})

I created GET_ID_FROM_DESC function in Oracle database (in my case I use HR and SH schema) and use it in EVALUATE function. This function returns CALENDAR_YEAR_ID for CALENDAR_YEAR as input.

The function code:

create or replace function get_id_from_desc(p_desc varchar2)
return number DETERMINISTIC
is
v_id number;
begin
select distinct calendar_year_id into v_id from sh.times where calendar_year=p_desc;
dbms_output.put_line(v_id);
return v_id;
exception when others then null;
end;

Maybe you'll need to create a function-based index on GET_ID_FROM_DESC(CALENDAR_YEAR), that's way I put DETERMINISTIC clause in function.

I found this information about DETERMINISTIC clause in rwijk.blogspot.com/2008/04/deterministic-clause.html:

Specify DETERMINISTIC to indicate that the function returns the same result value whenever it is called with the same values for its arguments.

You must specify this keyword if you intend to call the function in the expression of a function-based index or from the query of a materialized view that is marked REFRESH FAST or ENABLE QUERY REWRITE. When Oracle Database encounters a deterministic function in one of these contexts, it attempts to use previously calculated results when possible rather than reexecuting the function. If you subsequently change the semantics of the function, then you must manually rebuild all dependent function-based indexes and materialized views.

If you not specify DETERMINISTIC clause when you try to create function-based index you'll get an error:

ORA-30553: The function is not deterministic

So if you need, create function-based index on GET_ID_FROM_DESC(CALENDAR_YEAR):

create index times_fnc_idx1 on sh.times(hr.GET_ID_FROM_DESC(calendar_year));

Make a dashboard page and test report:



NQQuery.log:



select T20553.CALENDAR_YEAR as c1,
sum(T20550.AMOUNT_SOLD) as c2
from
TIMES T20553,
SALES T20550
where (T20550.TIME_ID = T20553.TIME_ID
and T20553.CALENDAR_YEAR_ID = GET_ID_FROM_DESC(2000))
group by T20553.CALENDAR_YEAR
order by c1

Example in this post was about how to choose description column from a dashboard and use id column in filter. Replace this example with your real case and test performance.