I am trying to query two databases in the following manner: select from the first database using a static query to get a list of values. Next, select from another database using the list of values returned from the first query as input to the IN-clause of the second query. Unfortunately I cannot figure out how to write the query so that the IN-clause is populated dynamically. Is this even possible? If so, would someone please provide me with an example?
Example:
On DB 1: select id from sometable where type = 1
On DB 2: select id from sometable where type = 1 and id not in ( )
Thank You,
Patrick
I tried various ways but it could not be realized. There is no way to pass the SQL string to "dbquery".
I like the example to give up.
|dbquery db1 "select id from sometable where type = 1"| search NOT[dbquery db2 "select id from sometable where type = 1"|format]
My workaround was to UNION both queries together returning a larger resultset than necessary, then filter out records based on a column in the resultset in order to find the subset of records that I was interested in.