I use a loop to build up a SQL string to be sent to SQL server via OLE DB. The loops works fine - when I "capture" the SQL before it gets sent to the database and then paste it into Query Analyzer - it runs and returns results.
If I limit the loop to generate a SQL string of less then 165,000 (approximate) characters it runs throught in QlikView, but over that the script fails withou error.
The question then is - is there a limit to the length of an SQL string the QlikView can handle.
Instead of creating the dynamic query in Qlikview, if it is possible try creating this in SQL stored procedure and execute the query in stored procedure. Now use this stored procedure in Qlikview to fetch the data.
I unfortunately don't have a solution for you, but I would appreciate it if you could share how you execute a SQL in text format in QlikView. I have the requirement to build up a dynamic SQL string and execute when QlikView loads. It sounds like you're already doing this? Would you mind sharing how?
Thank you for the feedback. I've tested this and it works well, thank you. However, it doesn't seem to work with joins - or I'm missing something. I get an ODBC read failed error. If I run the SQL in SQL Server it runs fine.
I'll have to build up some intense SQL. Would there be a way to still execute this via QlikView?
Basic SQL join example I tried to run:
LET lSQLString = 'SELECT * FROM W_MobileWeb_Dev.dbo.qmob_phcs LEFT JOIN W_MobileWeb_Dev.dbo.qmob_values ON W_MobileWeb_Dev.dbo.qmob_values.phcs_id = W_MobileWeb_Dev.dbo.qmob_phcs.id';
It actually doesn't; well spotted. The join created duplicate columns names - for example, both tables have an id column. I did a quick test with only certain columns and it seems to have worked:
LET lSQLString = 'SELECT W_MobileWeb_Dev.dbo.qmob_phcs.id, W_MobileWeb_Dev.dbo.qmob_values.id as value_id FROM W_MobileWeb_Dev.dbo.qmob_phcs LEFT JOIN W_MobileWeb_Dev.dbo.qmob_values ON W_MobileWeb_Dev.dbo.qmob_values.phcs_id = W_MobileWeb_Dev.dbo.qmob_phcs.id';
Thanks for your help. I'll test some sub queries next; hopefully they work as well.