workflow assistance-formulas and In-DB question

[sk1909]

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.

[christine_assaad]

Hi @sk1909 

You can use the Dynamic Input as shown below and paste your query under the SQL Editor tab

The text input I have could be replaced by your CSV and in the Dynamic input configuration, you can point Alteryx to the where clause and let it update the script with the values coming from your CSV as below. Value is the column header in the Text input that has the variables.

Hope this helps. Cheers!

[sk1909]

Thank you Christine!  I will give this a try!

[sk1909]

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.

[AbhilashR]

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.