Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Highlighted
paul_scotchford
Valued Contributor

Direct Discovery does not support SQL


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

4 Replies
MVP & Luminary
MVP & Luminary

Re: Direct Discovery does not support SQL

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

- Ralf

paul_scotchford
Valued Contributor

Re: Direct Discovery does not support SQL

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.

MVP & Luminary
MVP & Luminary

Re: Direct Discovery does not support SQL

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

paul_scotchford
Valued Contributor

Re: Direct Discovery does not support SQL

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.