Does anyone know if this is possible?
I’d like to use a SQL variable to select a list of account numbers from one database, then store the results, separated by commas, to be used in a "Select * Where in :List " statement. My goal is to greatly speed up an already existing Cross connection query. I’m currently trying to join a table of 10million records against about 500 records in another cloud database. I’ve tested this in the editor using static values in the list, but I’m looking to automate it somehow. Thanks!
I tried to find a way through various automation activities to build the delimited string, but could not find an answer. Thought perhaps the Loop Dataset activity would do the trick, but got a system error (index out of range) when attempting to use a Set Variable Value activity inside of it.
One solution I found was to call a function in an Execute Script activity that returned the delimited string, and then use that in a second Execute Script activity. I’m working with Oracle, so I created a function in a package that took no parameters and returned a string. It basically used a for loop on a cursor to build the comma separated list of ids, returning the completed string. I then called it via a select (SELECT my_package.my_function FROM DUAL) so that the result ended up in the variable Execute_1_SQL. Second step used the variable, as in SELECT * FROM OTHERTABLE WHERE OTHERTABLE_ID IN (#Execute_1_SQL#);. Looking at the log, I could see the substitution of the delimited list in the statement, and got the expected number of rows back.
Note that you can possibly simplify the first step by using PIVOT/STUFF/whatever in a single SQL statement and bypass going the whole stored procedure route, depending on what sort of database you are using and how current the release is.
HTH!
I figured it out! I’m pulling from Salesforce initially and couldn’t find a way to make it happen in that step, so I pulled the values into a single column in a Toad Local snapshot. Then I used a GROUP_CONCAT function with SEPARATOR inside a Set Variable SQL to create the string. From there I just passed that comma separated string variable into a SQL statement against DB2. Thanks!