Hi we are using OBIEE 11g. We have a prompt which is taking data from a table which has 100k records, some records were not showing up. When I checked the backend query that was generated, I was able to see something like this
SELECT "Customer"."Customer Name" saw_0 FROM "Sales"
WHERE UPPER("Customer"."Customer Name") LIKE UPPER('Rite%') ORDER BY saw_0
FETCH FIRST 65001 ROWS ONLY
The last part is actually limiting the number of rows fetched. I googled it and found few posts asking to change this value in EM->coreapplication->capacity Management->Performace and set "Max no of rows processed when rendering a table view" to a higher value than 6500.But this value is already set to 500k.
How do I get rid of this last limiting part of the query?
It looks like a limitation in the version we are using(11.1.1.6.7).
Raised an SR with Oracle and confirmed the same.
"Your SR indicates you're running version 11.1.1.6.7. Unfortunately, with this version, there is no way to increase the number of rows returned to your prompt. This was changed in all versions more recent than the one you're running. Please see: OBIEE 11g: Values Missing in a Column Filter Search Results List of Values when there are more than 65000 Rows Returned ( Doc ID 1554797.1 )"