workflow assistance-formulas and In-DB question

Question

I have a list of account numbers in excel that I am trying to pull additional data on from an oracle database. I have a sql i can run in toad to pull the data but I'm limited to 1000 records at a time by the DB admin through toad.  I have set up the InDB credentials in Alteryx, I just don't know where to go from here.

Here's my sql:

select b.s_org_id as account_id, c.first_name as contact_first_name, c.last_name as contact_last_name, c.phone as contact_phone, e.type as type, e.e_num as value, e.modify_stmp as modifiy_stamp from table_e_addr e, table_contact_role cr,table_bus_org b,table_site s,table_contact c where
s.objid = b.primary2site and c.objid = cr.contact_role2contact and cr.contact_role2site = s.objid and c.objid = e.eaddr2contact and b.s_org_id in 'account_id';

account_id is where I enter the multitude of account numbers in toad.

I'm lost from here.  Any assistance would be appreciated.

AbhilashR · Answer

Hi @sk1909, in case you are still looking for a solution to overcome the 1000 ID limit Oracle has, try the attached solution.

Following are my broad steps -

* Number the rows using the Record ID tool
* Break them into chunks of 1000 (Formula tool in my case)
* Using the Summarize tool - Concatenate the ID's and include the Group field using a GroupBy action 
* Pass the concatenated values to an Oracle SQL embedded inside a macro* Input tool inside my macro has a dummy file - replace/update it to point to your database connection
* Appropriately modify the Action tool config in the macro so as to replace a dummy text in SQL with the concatenated ID values.

I hope this helps.

OracleRead.yxzp

sk1909 · Answer

Sorry I'm just now getting back.  I haven't been able to figure out how to make this work.  I think I'm missing a step somewhere.

sk1909 · Answer

Thank you Christine!  I will give this a try!