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.

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.

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?

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.

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.