We want to add an extra where clause to a datasource query using the Pre-Query field in the datasource definition. We therefore created an action that uses API_DATASOURCE.setfilterwhereclause.
In the debug monitor we can see that the filter where clause is set, but it has no effect on the following query. Only when we do 'execute query' for a second time, the filter where clause is applied.
Is the Pre-Query event not the right moment to set a filter where clause?
asked 23 Nov '12, 01:29
OK. Now I understand. We are adding a new API called setQuery in version 1.2 which is exactly for this use case. Using this API you will be able to change the entire SQL of your datasoruce, based on the information provided by the user. So you could update your where clause easily using this API and keep the rest of the query the same. (Obviously, column names and their order should never change.)
setFilterWhereClause API is not ideal for your use case. If you use setFilterAPI, the original query will be executed and all the rows will be retrieved to the datasource. But only rows that pass through the filter will show up on the screen. There are rare use cases where this is desired behavior. But in general, this API should be used as a last resort.
As a side note: There seems to be a bug in Pre-Query which prevents the filter being applied. If you use Post-Query trigger it should all work.
So my suggestion is: Continue using setFilterWhereClause in the Post-Query trigger for now. Replace the whole thing with the setQuery API after you install version 1.2. Or if you can just wait a couple of weeks until 1.2 is out.
Does this make sense?
I don't know the case you are trying to solve, but it seems that a function returning rows using "pipe row" can solve your problem. Inside this function you can use dynamic sql and ref cursors. I have built many generic oracle reports using this technique.
answered 24 Nov '12, 12:02