topic Re: Direct Discovery does not support SQL in QlikView

Direct Discovery does not support SQL

paul_scotchford — Mon, 04 Nov 2013 21:59:21 GMT

Why does Direct Select not like SQL that contains Derived Tables, CASE's etc?

It seems to only allow simple Select .. from TableName .

Anyone out there doing anything beyond simple with DD?

Doco is lacking at this point

cheers

Paul

Re: Direct Discovery does not support SQL

rbecher — Mon, 04 Nov 2013 22:55:50 GMT

Just use a database view inbetween to encapsulate more database related SQL functionality..

- Ralf

Re: Direct Discovery does not support SQL

paul_scotchford — Tue, 05 Nov 2013 02:44:49 GMT

Yes I have tried this, however QV seems to spend a lot of time fetching from the view at QVW model load time.

The time to do this was over 3 hours for a view that would run natively in a matter of minutes. I'd be curious to know what QV does during model load ?

We will defer for now until SR5 is released as this is not workable for us.

Re: Direct Discovery does not support SQL

rbecher — Tue, 05 Nov 2013 09:57:46 GMT

QlikView DD will run one SQL query for each dimension column to build the symbol tables in this form:

SELECT MyDimField1 FROM MyTable GROUP BY MyDimField1;

SELECT MyDimField2 FROM MyTable GROUP BY MyDimField2;

SELECT MyDimField3 FROM MyTable GROUP BY MyDimField3;

etc.

This means it takes a lot of time if you have many dimensional fields and/or many records. And, it will nearly impossible to finish if you have large amounts of data or a Big Data source (probably never come back..) because this is running against the fact table! So, there are a few other ways to do this but in the moment we have no alternatives here like to do a query rewrite or provide own queries/data to feed the symbol tables..

Re: Direct Discovery does not support SQL

paul_scotchford — Tue, 05 Nov 2013 22:00:50 GMT

Thanks for the feedback Ralf, our QV consultant set me straight, I had coded all fields in the 'Direct Select' statement, which of course was me cutting and pasting , I only needed the key field in the Select, as all others are implicit per the DD documentation.