SQL Filter Issue - INSTR Function Causing Report Filters to Disappear for Specific Users

Question

Hi everyone,

we're experiencing an interesting issue with report filters in Oracle Learning Management (or similar).

Problem: When we use filters based on SQL queries, for some specific users the report filters disappear completely, making the report non-functional.

Technical Details: We've identified the critical point in the query that causes the error:

Copy

AND INSTR(',' || xxx.co_attribute1 || ',',           ',' || xle_le.legal_entity_identifier || ',') > 0

wlf_learning_items_f xxx

xle_entity_profiles xle_le

new 153.sql

Observed Symptoms:

* Filters work correctly for most users
* For specific users, filters disappear from the report
* The query doesn't return errors, but simply doesn't apply the filters

Questions:

* Has anyone else encountered similar behavior?
* Could it be related to NULL values or whitespace in the co_attribute1 or legal_entity_identifier fields?
* Do you have suggestions for better handling of this INSTR function in case of inconsistent data?

Any feedback will be greatly appreciated!

Thank you, Rosita

MandeepGupta · Answer

Hi Rosita,

I may not be fully clear on the issue.

Are you saying that when one of the parameters is driven by an SQL query and its output exceeds 56 characters, all the other parameters become greyed out? If possible, could you please share the SQL query used for the parameter along with a screenshot of the parameter configuration?

Thanks.

Rosita Santosuosso · Answer

Hi,

Thanks for your reply, but I disagree.

Our experience and testing demonstrate the opposite of what you say. If the output of my query (in the filter) returns a string longer than 56 characters, all other filters are blocked and grayed out, making them inaccessible.

Could you tell me why?

thanks

Regards

Rosita

MandeepGupta · Answer

Hi Rosita,

If your filters are at query level, then there is no restriction.

Thanks.