Qlikview : Direct discovery Oracle Error maximum number of expressions in a list is 1000

[sanveera]

I am using Direct Query in QVW. when i filter the records in Dashboards to below 1000 then the DIRECT Query works fine. when it exceeds the it is failing with the below error messages.

ErrorSource: OraOLEDB, ErrorMsg: ORA-01795: maximum number of expressions in a list is 1000

There is no IN clause or EXISTS clause in the DIRECT QUERY sql.

Please help me here.

[petter]

Direct Discovery generates SQL statements on the fly when using the dashboard. Even if you don't have an IN-clause in the DIRECT QUERY in the load script QlikView will nevertheless create a SQL similar to this:

SELECT DISTINCT SalesPersonID FROM THE_DIRECT_DISCOVERY_TABLE WHERE

CustomerID IN ( 1, 2, 3, 4, 5, 6, 7, 8, 9, 10 )

So depending on the number of values you select for your dimension you might exceed the number of elements allowed by Oracle - which is 1000.

You can turn on a feature called Sub Query for IN-clauses:

SET DirectEnableSubquery='true';

Then a query similar to this might be created:

SELECT

     "l_partkey", SUM("l_quantity") FROM "lineitem"

WHERE "l_partkey"

IN (SELECT DISTINCT "TPCH"."dbo"."PART"."P_PARTKEY" FROM

"TPCH"."dbo"."PART" WHERE "TPCH"."dbo"."PART"."P_DISCOUNT_FLAG" IN (‘Y') )

GROUP BY "L_PARTKEY"

Then the literal number of values limitation can be overcome.

Have a look at the Direct Discovery sections of the help:

http://help.qlik.com/en-US/qlikview/12.1/Subsystems/Client/Content/DirectDiscovery/direct-discovery-...

Specifically about subquery:

Using subqueries with Direct Discovery ‒ QlikView

[sanveera]

Hi Petter,

Thanks for this.

Still i am facing the issue. The link between DIRECT table and in-memory table based on a KeyID field. The distinct count of KeyID may go beyond 1000 in in-memory table too. if we do filter on in-memory fields, will this create this issue ?

[petter]

Did you try to Enable Direct Sub Query?

[sanveera]

Yes i did. Still i got the same error.

[petter]

You do have to make a data model that caters for selections that don't overrun the limitation of 1000 literal values in an IN-clause even if you use a sub query. In other words you have to tune the data model if you want to use the sub query compared to the first approach....