I am attempting to build an ETL script in the CloverETL Designer that, as part of its processing, builds a temporary table that it then uses it in a join. For reasons specific to the system a temp table that does not last outside of the transaction block is ideal, and so I am trying to avoid creating a view or standard table. Connections are being made over a JDBC driver to the databases involved.
I already know the SQL for both the temporary table and the query that follows using that table.
I am not, however, entirely sure what the best way is to do this under CloverETL. Can I get a DBExecute to run in the same transaction block as a DBInputTable? Is my best bet going to be to bring both tables into the local database and then work with them from there?