I am trying to run a report that access two different universes and I am using Account ID as the Unique identifier for both.
I want my second query to only return results that link to the first query and I have done this through using “Account ID In List of the query result first universe.account ID” in my condition on my second query.
However, I am getting the error ORA-01795: maximum number on the list is 1000. I was told that this could be cause of the ‘In list’ function that I am using, but I have tried ‘equal to’ as well, i.e.“Account ID Equal to of the query result first universe.account ID” and it still throws up the same error.
Is there any way to get around this? Anyone come across this?
This is an Oracle problem. In Oracle, there can’t be more than 1000 values in the IN operator. Ask your Oracle DBA if anything can be done about it.
A possible work around can be to change the logic of your data providers (DP):
Instead of using results of DP1 in the condition of the DP2, have just 1 DP where the condition will be created as a subquery.
A possible work around can be to change the logic of your data providers (DP): Instead of using results of DP1 in the condition of the DP2, have just 1 DP where the condition will be created as a subquery.
Thank you very much for your advice. Making changes to the Oracle is quite cumbersome where I work. But I have started to look at changing the logic of my DP. The problem that I am facing now is that I am using two universes and from what I can see, I can’t seem to incorporate the 2nd universe into my subquery in DP1. I might be missing something. Any ideas on this?
As mentioned in the first messages, it is probably caused by a filter on a data provider that grab result from another data provider in a IN (...) filter. And Oracle database doesn’t allow to have more than 1000 items in this filter.
Can you try to change a bit the logic in order to remove this filter ? Maybe set a subquery filter ?
Are you facing this issue in Web Intelligence (Webi) or Desktop Intelligence (Deski)? If in Deski, many of us would be wondering, “Why are you still using Deski?”
If in Webi, create a subquery in the filter area, that should resolve it.
Than you Julien and Mohammed, I tried subquery option and continue to have ORA-01795 message. What I’m trying to do is pulling specific information using an ID from another universe, unfortunately is more than 1000 ID on the 1st universe.
(BOB member since 2003-11-27)