Please refer to the photo at my dropbox, https://www.dropbox.com/s/baykeb1xdrpdjcn/insert_issue.jpg?dl=0
For some reason I cannot upload any photo or pdf attachment.
Basically i need to run a MERGE statement using Advanced SQL widget but for some reason it does not work as expected. So I tested it with a simple INSERT statement but to my surprise, whatever row i inserted via the advanced SQL disappeared. It appeared to me that the rows were probably rolled back.
With regards to the photo, please take note that I started with JUST a simple INSERT statement. I did not add the second SELECT statement yet. I added it only after it failed to work.
Is this an expected result? Is MERGE and INSERT disallowed?
Is it possible for you to upload your sample? If you can, that would make it easier to help over the just a photo. You shouldn't need to use the advanced SQL to do an insert. I am not sure what would happen in practice trying to insert and select at the same time.
Hi Christopher,
What is happening is that you are testing the SQL with the Test button, right?
When you do this, the query is effectively executed in the database, so your select, after the insert, given your output structure is correct, will show that the row was inserted.
But as soon as this ends, as this is just a test, it will abort the transaction and roll back any change.
That's the reason why you're not seeing the results being persistent.
If you create a page with a button to can a screen action to execute this query from the browser, assuming no errors in the query, it should be persistent in database.
Cheers
P. S. It is perfect possible to execute multiple statements like this one, but pay attention that if you have multiple selects, If I remember well, the output will be from the first one. I think. :)