0

I have input from a tableA in database A that I would like to join to another tableB in database B.

These were my two options:

  1. Use Database Join: For each input from table in database A, run the join query in database B.
  2. Use two Input tables (talbeA + tableB) and do merge join on key.

I went with option #1 as I want to avoid reading in tableA and tableB in entirety.

My question is: How can I use all results from a prior step as one "IN" query?

For instance

select *
from tableB b
where b.id IN (all_rows_from_prior_step)

versus (where it runs for each input row)

select *
from tableB b
where b.id = ?
SRS
  • 15
  • 1
  • 6

1 Answers1

0

Use 'Group by' to flatten the rows into one row with a field 'all_rows_from_prior_step' of comma separated ids (Group field: empty, Name: all_rows_from_prior_step, Subject: id, Type: 'Concatenate strings separated by ,'). Next, use a 'User Defined Java Expression' to build the sql query:

"select * from tableB b where b.id IN (" + all_rows_from_prior_step + ")"

Last, use 'Dynamic SQL row' to run the query. The template sql could be

select * from tableB b where 1=0

matthiash
  • 3,105
  • 3
  • 23
  • 34
  • I followed as detailed in your explanation, however, if I have ten input rows, I ended up with ten IN query being ran at the end. It looks like 'Concat Fields' is not flattening the rows into 1 row for me. I played with different options in 'Concat Fields' but no dice. – SRS Aug 02 '16 at 21:26
  • Sorry, my explanation was wrong. See updated explanation. – matthiash Aug 03 '16 at 07:34
  • Updated explanation is doing exactly what I was looking for, I end up with one nice IN query! – SRS Aug 03 '16 at 16:36