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

[#1]

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?

[kuba_michalik]

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.

[Anonymous]

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

[#4]

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.

[#5]

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.

[JonnyPoole]

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

;

[kuba_michalik · Accepted solution]

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.

[#8]

That works!

Thanks!!

[ceciliafujita]

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?