Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
QlikWorld, June 24-25, 2020. Free virtual event for Data Integration and Data Analytics gurus. Register Today
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Not applicable

Can we force QlikView to understand a semi-colon as a character?

I am trying to run a SQL Merge command in a QlikView 11.2 script but I am not able to make it run successfully.

The problem is SQL Server demands that the MERGE command ends with the semi-colon but if I use the semi-colon in the QlikView script, QlikView interprets the semi-colon as the escape character and does not send it to SQL.

I tried to create a variable and concatenate to Chr(59), run inside quotes or single quotes but none of this work or any combination of these approaches.

Create a stored procedure is not an option.

Use another SQL command that is not Merge is not an option.

Any ideas?

Tags (1)
1 Solution

Accepted Solutions
Highlighted
Partner
Partner

Re: Can we force QlikView to understand a semi-colon as a character?

All right, I get you now. Had to test to understand it, apparently MERGE is oh so special

Do it this way:

SQL EXECUTE (

'MERGE

     rest of the merge statement remember to escape single quotes

;');

Edit: unless using EXECUTE is not an option, as technically this *is* another command, although the result is exactly the same and it's executing the exact same code, just with an added level of indirection. In which case, I have no idea.

View solution in original post

8 Replies
Highlighted
Partner
Partner

Re: Can we force QlikView to understand a semi-colon as a character?

Doesn't every valid SQL statement, including SELECT, have to end with a semicolon? I always thought that everything that is between the SQL keyword and the semicolon gets sent to the database straight as it is. Unless you mean there is a non-syntactic semicolon somewhere in the MERGE statement and QlikView insists on treating it as the statement terminator? Or are you trying to sent two statements to the database within a single SQL (...) ; block?

If you could paste a script sample, that should clarify things.

Highlighted
Creator III
Creator III

Re: Can we force QlikView to understand a semi-colon as a character?

Hi,

maybe your problem is:

"Sending SQL statements which update the database will return an error if QlikView has opened the ODBC connection in read-only mode"

as merge tries to manipulate the database?

Best regards

Stefan

Highlighted
Not applicable

Re: Can we force QlikView to understand a semi-colon as a character?

I need to send the semi-colon to SQL but QlikView does not do it because treat it, as you said, as the statement terminator.

Highlighted
Not applicable

Re: Can we force QlikView to understand a semi-colon as a character?

That is not the problem. If I use the same connection and run an INSERT or UPDATE, it does work fine. The problem is Merge statement in SQL Server must end with the semi-colon but QlikView does not send the semi-colon to SQL as part of the statement.

Employee
Employee

Re: Can we force QlikView to understand a semi-colon as a character?

could you post the load script ?

I tried to replicate the issue in various ways with SQL but could not. I have not tried a 'merge' however. 

The following uses a semicolon in a string in two ways in the SQL , but it works for me without error... only the last semicolon ends the SQL

ODBC CONNECT TO [Salesman;DBQ=C:\Qlik Resources\Demos\10 Minute Demo\Data\Salesman.mdb];

LOAD `Salesman ID`,

    Salesman,

    `Distributor ID`,

    semicolon;

SQL SELECT

  *,

  ';' as semicolon

FROM SALESMAN

where Salesman <> ';'

;

Highlighted
Partner
Partner

Re: Can we force QlikView to understand a semi-colon as a character?

All right, I get you now. Had to test to understand it, apparently MERGE is oh so special

Do it this way:

SQL EXECUTE (

'MERGE

     rest of the merge statement remember to escape single quotes

;');

Edit: unless using EXECUTE is not an option, as technically this *is* another command, although the result is exactly the same and it's executing the exact same code, just with an added level of indirection. In which case, I have no idea.

View solution in original post

Highlighted
Not applicable

Re: Can we force QlikView to understand a semi-colon as a character?

That works!

Thanks!!

Highlighted
Contributor III
Contributor III

Re: Can we force QlikView to understand a semi-colon as a character?

Hi,

If I'd understood I had the same issue here.

Merge requires a semicolon when it ends. But, sometime your query is not ended yet.

example:

//----------------------------------

LOAD *;

SQL

     CREATE TABLE #table ...

     INSERT INTO #table SELECT...

     MERGE INTO #table

        USING (

           SELECT ... ;

     SELECT * FROM #table

     DROP TABLE #table ;

//----------------------------------

As Jakub said, you can use merge like this:

LOAD *;

SQL

     CREATE TABLE #table ...

     INSERT INTO #table SELECT...

     EXECUTE('MERGE INTO #table

        USING (

          SELECT ... ;')

     SELECT * FROM #table

     DROP TABLE #table ;

//----------------------------------


My issue is that I have a variable inside the merge... what can I do in this case?