topic Re: QlikView SQL string length limit in QlikView

QlikView SQL string length limit

jarno_loubser — Tue, 13 Dec 2011 07:43:41 GMT

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.

QlikView SQL string length limit

jarno_loubser — Fri, 02 Mar 2012 05:44:31 GMT

Any ideas from anyone?

QlikView SQL string length limit

jvitantonio — Fri, 02 Mar 2012 11:16:19 GMT

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

Re: QlikView SQL string length limit

jagan — Fri, 02 Mar 2012 12:36:10 GMT

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.

QlikView SQL string length limit

jarno_loubser — Mon, 12 Mar 2012 06:46:47 GMT

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

Re: QlikView SQL string length limit

Thu, 06 Mar 2014 07:10:20 GMT

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

Re: QlikView SQL string length limit

rbecher — Thu, 06 Mar 2014 07:18:37 GMT

Hi Johan,

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

$(vTableName):

SQL $(vSELECT);

- Ralf

Re: QlikView SQL string length limit

Thu, 06 Mar 2014 07:26:14 GMT

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

Re: QlikView SQL string length limit

rbecher — Thu, 06 Mar 2014 07:43:23 GMT

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

Re: QlikView SQL string length limit

Thu, 06 Mar 2014 07:57:24 GMT

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

Re: QlikView SQL string length limit

rbecher — Thu, 06 Mar 2014 08:01:19 GMT

Yea, you should always use a column list, not SELECT *.

Re: QlikView SQL string length limit

Thu, 06 Mar 2014 08:05:42 GMT

Agreed, thanks.

One more question. Any idea how I could do the following:

LET lSQLString = 'SELECT REPLACE(REPLACE(W_MobileWeb_Dev.dbo.qmob_values.value_comments,';','-'),':','-') AS value_comments 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);

The quotes in the SQL statement are causing the quotes in QlikView to indicate the string are conflicting.

I've tried adding quotes, but without success.

Any ideas?

Regards,

Johan

Re: QlikView SQL string length limit

rbecher — Thu, 06 Mar 2014 08:10:21 GMT

This should work with doubling the quotes inside the string:

LET lSQLString = 'SELECT REPLACE(REPLACE(W_MobileWeb_Dev.dbo.qmob_values.value_comments,'';'',''-''),'':'',''-'') AS ..'

- Ralf

Re: QlikView SQL string length limit

Thu, 06 Mar 2014 08:13:07 GMT

Excellent!!! Thank you Ralf, your assistance is MUCH appreciated!