Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

Extracting results for few records from large SQL Table

soumen
6 - Meteoroid

I have a SQL Table suppose XYZ which contains more than 2 Million data rows. I need information for 10k data points which I hold in an excel sheet. Now I can use Join with Primary key and get the data which takes longer time as my main table in holding huge number of data rows. Any other way which could find only those 10k records within shorter time?

 

Thanks in Advance

Soumen  

6 REPLIES 6
AbhilashR
15 - Aurora
15 - Aurora

@soumen - you could embed table XYZ's SQL inside the Dynamic Input tool, and pass the 10k ID's (from excel file) to this SQL during run-time to fetch data only for those records. In other words, once you embed table XYZ's SQL inside the Dynamic Input tool, use its Modify SQL query option to replace a specific string of this SQL (in this case your 10k ID's).

 

The suggested solution is somewhat dependent on your database type (specially with Oracle), and how well is your SQL written. Oracle I think has a limit of accepting only 1000 ID's in its Where clause - in which case you can embed the Dynamic Input inside a Batch Macro and pass the 10k ID's to this macro in batches of 1000.

 

Capture.PNG

Hope this helps.

soumen
6 - Meteoroid

@AbhilashR Thanks for the solution. Your are right that Oracle does not allow more than 1000 records and I am using the same. But I cant find the Dynamic Input tool in user interface.

 

SS attached.Could you check why?

 

Thanks

Soumen

AbhilashR
15 - Aurora
15 - Aurora

@soumen - on second thoughts you probably could get away with simply using a simple Input tool. I have attached a simple proof of concept here to help you setup your solution. In here:

  • Replace my Text Input tool with your excel file containing ID's
  • Update the Summarize tool to replace my Field1, and Concat your ID field
    • make sure you mimic the Concatenate Properties shown in the screenshot below
    • Capture.PNG
  • Select the appropriate field in the dropdown in the macro
  • Inside the macro oracleREAD.yxmc
    • setup the necessary oracle connection in the input tool
    • then paste your sql in the SQL editor and add a line in the WHERE clause where you enter something like
      • AND REFERENCE_LOOKUP_ID in ('DUMMY')
  • Save the Macro and go back to your main workflow and run.

This should hopefully get you the solution you need. 

 

Like most things in Alteryx, there are multiple ways to achieve the same goal - you could use In-Databse tools as well, but try this one to begin with.

chinnashleyn1
5 - Atom

@

 

I tried recreating the workflow you explained however I am running into difficulty with the macro part. I am getting no output after running the workflow as if it stopped running and didn't even run through the macro. When I run the batch macro on it's own it seems to work so I don't think it is the SQL that is incorrect. Would you be able to show what your macro would look like?

Here is my workflow:

chinnashleyn1_2-1614359984122.png

 

Here is what my macro looks like:

chinnashleyn1_0-1614359384230.png

 

AbhilashR
15 - Aurora
15 - Aurora

Hi @chinnashleyn1, below is a screenshot of how I had setup the macro

AbhilashR_0-1614546580903.png

 

In reference to your workflow setup, can you confirm if the field you are passing into the macro is replacing the appropriate section of the SQL inside the macro? 

chinnashleyn1
5 - Atom

@AbhilashR, I was able to get it to work using your example. Thanks for your help.

Labels