SYMPTOM:
When executing a report which results in more than 1000 elements in "WHERE" clause of the SQL statement in Strategy Desktop 9.2.1 against Oracle 10gR2 warehouse, the following error appears:
Sample Code/Error
QueryEngine encountered error: Execute Query failed.
Error type: Odbc error. Odbc operation attempted: SQLExecDirect. ORA-01795: maximum number of expressions in a list is 1000
CAUSE:
This is a limitation in Oracle database. The maximum number of expressions in a list is 1000.
WORKAROUND:
In Strategy, try the workaround below:
Create a Freeform SQL report, split the original "WHERE" clause from one "IN" condition to several "IN" condition, as shown below:
Sql
select TID from TA where TID in (1,2,...1000,1001)
To:
Sql
select TID from TA where TID in (1,2,...500) or TID in (501,...,1001)
