Dymanic Update Queries (Iterative Macro) - Automation

[singh28ashish]

Hello All,

I have a requirement to automate task. We are having around 30 oracle queries. Which i need to automate . I used Iterative Macro and dynamic input to update queried automatically. I am using OCI connection string. Attaching sample workflow for reference with sample data.

Please guide me where I am doing wrong.

Thanks in Advance.

[singh28ashish]

.

[Yoshiro_Fujimori]

Hi @singh28ashish ,

Could you share the error messages ?

Your input data in the excel file has the following two SQL scripts. (New lines are added for readability)

It seems that both scripts are not complete (the column names are blank), which I guess may cause the error on the Oracle DB side.

select Rownum as REFERENCE_CODE,     CTR_NAME as ABBREVIATION,     CTR_ISO_CD as CODE_LABEL,     'Y' as VALID,     '' as DESCRIPTION,     '' as CHILD_REFERENCE_TYPE,     '' as CHILD_ATTRI\nBUTE_TYPE from country

select ROWNUM as REFERENCE_CODE,     SGR_NAME as ABBREVIATION,     upper(regexp_replace(SGR_NAME,'[^a-zA-Z0-9]','_')) as CODE_LABEL,     'Y' as VALID,     '' as DESCRIPTION,     '' as CHILD_REFERENCE_TYPE,     '' as CHILD_ATTRIBUTE_TYPE from sales_Tax_group

[singh28ashish]

Thanks for your reply @Yoshiro_Fujimori . Query are same and running fine. Just wanted to automate the process so all 20 - 30 queries execute one by one.

[Yoshiro_Fujimori]

Well, then I would use Batch macro instead of Iterative macro.

(Check the interactive lesson for Batch Macro if necessary)

You may want to

1. Replace the Macro Input Tool with Control Parameter Tool.

2. Connect it with Dynamic Input Tool.

3. With Action Tool after Control Parameter Tool, update the SQL strings with the data from Control Parameter Tool.

As I do not have access to your database, I cannot touch your workflow.

Good luck!

[singh28ashish]

still not clear. if any dummy workflow on how to run multiple sql queries in Alteryx. It would be really helpful.

[jdminton · Accepted solution]

@singh28ashish a couple of notes: the macro in your workflow is set to a batch macro (not iterative) and you don't use a control parameter. I've updated to use a control parameter and an input data tool (versus dynamic, which is not needed in the macro). Also, I did have to change the connection since I don't have access to your Oracle DB, so you will need to connect how you normally would. Use a simple SQL in the input data tool in the macro and select the SQL to be replaced as below.