Write to Database and Use Table as Input to Create Final Table

[anm252]

Hi guys—hoping I can clearly get across what I’m trying to accomplish. I have two different Oracle databases I’m working with. The first SQL does most of the work and produces all but two fields. I get those last two fields using a SLQ from my second database based on the output of the query from the first database. It’s pretty simple as I only need the claim number from the larger dataset to left join and pull in the two extra fields so right now I have it set up as a dynamic input for the SQL of my second database. I obviously can’t run my second SQL until the first is complete (maybe using IN DB create a temp table?) The problem is that there are 400,000ish claims and the dynamic input tool takes so long. Is there a better way to do this? I’ve looked around on here and seen IN DB tools suggested many times but am not sure how I’d go about it. Here is what my current workflow looks like as it’s set up.

 

[apathetichell]

dynamic input in-db is the better way. you should be creating a massive where .... in (value1...valuen) - using a summarize tool. you can pass that in as your query and that should limit your join/output/whatever to what you need.

[anm252]

So I would write my main SQL query first using an In DB Connection then use a dynamic input in-db to write my second query? How would I create a massive where statement using a summarize tool? Thanks for the info! Let me know where I’m off here.

[apathetichell]

sorry should be "a massive where clause..." this assumes you have data in twodbs/warehouses/etc.

if you only have data in 1 - use connect in-db twice. use a join. 

if you have data in 2 - run your main query - get your join criteria into your canvas in rows. use summarize and formula to contruct your

query. feed it into dynamic input in-db to create your effective cross-db join. put your data back on your canvas (or back in your other db via datastream in) as needed.

for a massive where clause:

get the value you want in your field. use a summarize tool in concat mode with (" "," ") set as your start, seperator and end (assuming text). use a formula tool after that to build your query. use a second formula tool to declare your connection name.

[anm252]

Sorry I think I"m a little lost here. Any way you could visualize the workflow using a Query/Database#1 and Query/Database#2?