Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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:
Specifically about subquery:
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 ?
Did you try to Enable Direct Sub Query?
Yes i did. Still i got the same error.
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....