QlikView SQL string length limit

[jarno_loubser]

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.

[jarno_loubser]

Any ideas from anyone?

[jvitantonio]

I didn't find any info on this. I suggest you open a ticket to QlikTech team.

[jagan]

Hi,

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.

You can execute stored procedure using

SQL EXEC StoredProcedureName;

Regards,

Jagan.

[jarno_loubser]

Thank you Jagan. This will work, but it will require some rework and make the solution very database specific.

[#6]

Hi Jarno,

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 and regards,

Johan

[rbecher]

Hi Johan,

you could do it in this way, having the table name in a variable and the SELECT statement:

$(vTableName):

SQL $(vSELECT);

- Ralf

[#8]

Hi Ralf,

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';

SQL $(lSQLString);

Regards,

Johan

[rbecher]

Does the statement itself (no in variable) runs in QlikView?

[#10]

Hi Ralf,

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';

SQL $(lSQLString);

Thanks for your help. I'll test some sub queries next; hopefully they work as well.

Kind regards,

Johan