I am trying to send an update statement to PostGres, with a dynamic substitution in the syntax, as I have done before with "regular" MS SQL.
So, I took a delete syntax from another MSSQL that work nicely (see attachment) with the query in the input data source:
SELECT 1 as Deleted;
delete from Data.Responses where ResponseId in (RESPONSEIDSREPLACE);
And then a "Modify SQL Query" replacing the string RESPONSEIDSREPLACE with a concatenated string holding the relevant IDs.
Works nicely with MSSQL. So I took it and reworked a bit to use for PostGres:
SELECT 1 as Update;
update "Input"."SurveyTransactions" set "InitStatusId" = '2' where "InitStatusId" = 'STATUS';
Where STATUS being replaced with my concatenated string of relevant IDs.
This fails though, with the errror:
Dynamic Input (111) Error SQLPrepare: [Simba][PostgreSQL] (30) Error occurred while trying to execute a query: [SQLState 42601] ERROR: syntax error at end of input¶LINE 1: SELECT * FROM ¶ ^¶
Seems like PostGres needs me to specify a table instead of a query?
So, I tried pointing to the specific table here, and then moved my syntax to the "Post SQL Statement" field instead, but then it seems the replacement of my string (STATUS) doesnt work
(Error occurred while trying to execute a query: [SQLState 22P02] ERROR: invalid input syntax for type integer: "STATUS")
...so, how do you people dynamically send updates (or delete statements) to PostGres?
