Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

jarno_loubser
Contributor

QlikView SQL string length limit

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.

13 Replies
jarno_loubser
Contributor

QlikView SQL string length limit

Any ideas from anyone?

jvitantonio
Valued Contributor III

QlikView SQL string length limit

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

MVP
MVP

Re: QlikView SQL string length limit

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
Contributor

QlikView SQL string length limit

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

Not applicable

Re: QlikView SQL string length limit

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

MVP
MVP

Re: QlikView SQL string length limit

Hi Johan,

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

$(vTableName):

SQL $(vSELECT);

- Ralf

Not applicable

Re: QlikView SQL string length limit

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

MVP
MVP

Re: QlikView SQL string length limit

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

Not applicable

Re: QlikView SQL string length limit

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

Community Browser