subquery alternative

Question

Hello,

I have a situation where there is no database link between 2 oracle databases that are in different servers but want to leverage values returned by a query on one database inside an in operator in another query in another database. I can do a join if it was straight forward query but these are complex queries.

So I will need to get the string values returned by a query and use them in an operator in another query.

Is this possible in Alteryx designer?

Thanks in advance

Yuri24 · Answer

Thanks mst3k. Few things on why I am not able to do this,

1. I am using alteryx designer v19 and also for this setup I am using ODBC database connection

2. The query that feeds the input to the other query returns around 1000 values (guids)

3. actual query in which I am updating the where clause is a common table expression query and it has a where in the with part and another in the select part so when I click on Update where clause the designer freezes and then goes into not responding mode for ever. But if I use replace itemid with an object then for each guid the query is running once and it takes few hours to complete (considering almost 1000 guids)

How should I make it not freeze and run fast?

mst3k · Answer

from the output of your first query, say you have ItemID in the results.

then in your second query, in the Dynamic Input tool, you may have something like this:

WHERE

ItemID in ('REPLACE')

go to the Dynamic Input tool configuration with the SQL, change to Modify SQL query. then use the Add dropdown, and you'll likely want "SQL: Update a WHERE clause", and hopefully you'll see "ItemID in ('REPLACE')" already there in the dropdown for you, select it and select which field streaming in has the ItemID. if you DON'T see your where clause in the dropdown, it's probably got another function on it (like trim(ItemID) in 'REPLACE') and in that case you can just type/paste it in instead of using the dropdown. I'd recommend enabling "Group Replacement Value for SQL IN Clause" since it sounds like you'll have a list of values passing through here. It will run the SQL in batches, replacing that IN clause in however many batches it takes to cycle through your list of values, subject to the Character Limit for IN Clause. That defaults to 1000 - note it's a CHARACTER limit, not an item limit. If the ItemID is 7 digits long, then account for the ' and , between them you'll have 10 characters for each ItemID in the list. So the default of 1000 characters would run the query in batches, with 100 ItemIDs at a time (1000/10). You can alter that 1000 character limit default if you want, but you'll need consider your query in how increasing or decreasing it might improve performance.

Yuri24 · Answer

Is there another way to handle this? Thanks in Advance