Pass the value of a first sql query into the second query

Question

I connected to the SQL database and got results using a SQL query.

Now I have to connect to the DB2 database using a query but in where condition of the query, one of the column names of DB2 table equals to the value from the first query from SQL.

Please advise how to get this through

Example:

SQL query: Select col1, Col2, Col3, Col4 from Tbl1 where creationdate between 01-Dec-2020 and 14-Dec-2020

DB2 query: Select Col5,Col6,Col7 from Db2TBL2 where col1=(col1 value from SQL DB)

BrandonB · Answer

I would normally suggest using a Data Stream In tool to send the results of the first query up into a temp table of the next database and then do a subsequent Join In-DB. Because your second database is DB2, In-DB won't be supported, but there is another way that you can accomplish this.

The Dynamic Input tool will allow you to pass a column of values into an IN statement in your where clause. So for the template, just have it execute the select statement that you want and say where col1 IN ('dummy'). Then you can use the option SQL: Update WHERE Clause in order to pass in the upstream values into your subsequent SQL query as shown below. Make sure to check the box for "Group Replacement Value for SQL IN Clause" so that it automatically takes all of the values in the column you are passing in and turns them into a comma separated string to be executed as one query rather than separate.