Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
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.
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.
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
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.
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.
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 <> ';'
;
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.
That works!
Thanks!!
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?