How to solve (data source adapter error: java.sql.SQLSyntaxErrorException: ORA-01795: maximum number-CodePudding

data source adapter error: java.sql.SQLSyntaxErrorException: ORA-01795: maximum number of expressions in a list is 1000 I am getting this error in cognos.Basically I am working on report enhancement in cognos.I already have searched for solution on internet but provided solution is for oracle, i want solution to be in cognos as i am unable to access database or framework manager. Please let me know what should be the right approch to resolve that error. enter image description here

I already have searched for solution on internet but provided solution is for oracle. I want the solution to be in cognos as i am unable to access database or framework manager.

CodePudding user response：

Error you got is indeed raised by Oracle, but the question is whether it should be fixed in Oracle or elsewhere (Cognos?).

Generally speaking, this is what happened:

select whatever from some_table
where id in (1, 2, 3)    --> this is the IN clause. In Oracle, it is limited
                             to 1000 elements

I guess that nobody literally types 1000 elements into the IN list, but some dynamic piece of code might, concatenating value after value until you reach and pass the limit.

I don't know Cognos, but - if that's what really happens, a simple and effective option is to store values which are being used in IN list into a table, and then either JOIN that table to other table(s), or use it as a subquery.

For example:

select whatever from some_table
where id in (select id from a_new_table)

select whatever
from some_table a join a_new_table b on a.id = b.id

CodePudding user response：

Presumably the filter is coming from a prompt and someone is selecting everything in the list in the prompt or close to it.

It might be a good idea to try to figure out why there are so many things being chosen. This would require investigation about the business purpose of the prompt.

It might be that our someone needs to see everything so the prompt should be set as optional, which would not generate the where predicate in the SQL.

It might be that the prompt needs to be generated on an attribute (column) which is at a higher grain of dimensional abstraction (i.e. countries rather than cities).

Simply being told fix it in Cognos is not a very helpful instruction and you need to approach the problem with a better understanding of the purpose of the report.