Direct Discovery does not support SQL

[paul_scotchford]

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

[rbecher]

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

- Ralf

[paul_scotchford]

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.

[rbecher]

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]

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.