I have done this before successfully. You make a first call that populated a temp table on the source DB Then a 2nd call to read the results from that temp table.
call: SQL call myprocedure_sp('results');
actuals: SQL Select * from results;
However, this case is different. There is statement in the SQL : drop table if exists #mytemp; Redshift naturally produces a warning here : Table "#mytemp" does not exist and will be skipped
All my other clients don't care about this, it is only a "Warning", but QlikSense does care.
QlikSense Error: The following error occurred:
ERROR [01000] INFO: Table "#mytemp" does not exist and will be skipped
.. and ultimately refuses to go any further.
So, I know that there are measure I can take on the Redshift side, involving creating UDFs to handle an 'exists' check,
but is there anything I can do on the QlikSense side ? Tbh allowing the procedure to spawn warnings is very useful for developers and we are not entirely comfortable suppressing them.
@simonB2020 I don't have a way to try this out, but I am wondering if you can do something with ErrorMode and ScriptError here. Perhaps set ErrorMode to 0 so that it does not halt processing and then you examine ScriptError and decide what you want to do next?
