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

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

sanveera — Mon, 09 Jan 2017 10:01:09 GMT

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.

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

petter — Mon, 09 Jan 2017 11:47:11 GMT

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-introduction.htm

Specifically about subquery:

Using subqueries with Direct Discovery ‒ QlikView

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

sanveera — Mon, 09 Jan 2017 14:29:51 GMT

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 ?

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

petter — Mon, 09 Jan 2017 23:54:47 GMT

Did you try to Enable Direct Sub Query?

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

sanveera — Tue, 10 Jan 2017 03:49:28 GMT

Yes i did. Still i got the same error.

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

petter — Wed, 11 Jan 2017 03:25:25 GMT

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....