Hello All, I am working on a Alteryx Process In which I need an help. * I have one SharePoint list and on the SharePoint List all the SCHEMA NAMES is uploaded. * I have to fetch some columns from ORACLE Database where all the column name will be same only SCHEMA NAME will change every time. * I implemented the SQL query template into Dynamic Query tool. SCHEMA_NAME_TABLESchema_1Schema_2Schema_3Schema_4 SQL Query:- Select * from SCHEMA_NAME.test; In the SQL Query I have to replace the only "SCHEMA_NAME" every time from the SCHAME_NAME_TABLE. Can you please help me with these scenario. If possible kindly share the sample workflow as well.

Replace The SCHEMA NAME into Dynamic Input Tool

shashank_shukla

Hello All,

I am working on a Alteryx Process In which I need an help.

I have one SharePoint list and on the SharePoint List all the SCHEMA NAMES is uploaded.
I have to fetch some columns from ORACLE Database where all the column name will be same only SCHEMA NAME will change every time.
I implemented the SQL query template into Dynamic Query tool.

SCHEMA_NAME_TABLE

Schema_1

Schema_2

Schema_3

Schema_4

SQL Query:- Select * from SCHEMA_NAME.test;

In the SQL Query I have to replace the only "SCHEMA_NAME" every time from the SCHAME_NAME_TABLE.

Can you please help me with these scenario.

If possible kindly share the sample workflow as well.

Connectors

Dynamic Processing

Database Connection

Accepted answers

patrick_mcauliffe

Ohhh.. yep, I remember this one now.

One of those schemas must have a differently named column or a column of a different type with the same name.

You'll have to build this one out of a batch macro.

Create your Data Input tool like normal by writing that query on any one of the schemas.

Then add a Control Parameter and connect it to the Data Input tool.

The Update tool that gets created when you make that connection should have all of the replaceable information in it.

Just like you did before, only replace the schema name.

Attach a macro output to the Data Input to get the data out of the macro.

To deal with conflicting schemas, you need to change a setting in the interface settings. Hit Ctrl+Alt+D and go to the last tab of the resulting window.
There are options there to allow the schema to change between iterations - you want to have that set.

patrick_mcauliffe

Okay, let's say I have my query here in a normal Input Data tool:

Now go to the Interface tools and grab a Control Parameter onto the canvas:

When I drag the magnifying glass on the Control Parameter to the Lightning Bolt on the Data Input, the canvas automatically adds an Update tool:

If you click the "File" section in the Update tool's config you can change which section of the query to update, just like you would in the Dynamic Query:

Now, add a macro output:

And when I hold Ctrl+Alt+D, I get the Interface Designer window:

At the bottom of those left icons on the Interface Designer window is a gear. This is the one you want.

Change those settings such that "Output fields change based on macro's configuration or data input"

and "Auto Configure by Name"

Now, let's save this macro.

Then, open the canvas where you have a list of schema names.

Right click on the canvas, go to Insert --> Macro --> and select the one you just saved:

Next, set your control parameter to match the input schema names:

That's it!

All comments

patrick_mcauliffe

What you've described should work.

Are you getting an error message?

shashank_shukla

Hey @patrick_mcauliffe ,

While applying this process in Alteryx I am getting "Has A different SCHEMA Than the 1st file".

This error.

While running for Single Query its running and replacing the SCHEMA_NAME but for multiple rows its now working.

patrick_mcauliffe

Ohhh.. yep, I remember this one now.

One of those schemas must have a differently named column or a column of a different type with the same name.

You'll have to build this one out of a batch macro.

Create your Data Input tool like normal by writing that query on any one of the schemas.

Then add a Control Parameter and connect it to the Data Input tool.

The Update tool that gets created when you make that connection should have all of the replaceable information in it.

Just like you did before, only replace the schema name.

Attach a macro output to the Data Input to get the data out of the macro.

shashank_shukla

Hey @patrick_mcauliffe ,

I am not getting any of the option which is mentioned by you while creating a Workflow.

patrick_mcauliffe

Okay, let's say I have my query here in a normal Input Data tool:

Now go to the Interface tools and grab a Control Parameter onto the canvas:

When I drag the magnifying glass on the Control Parameter to the Lightning Bolt on the Data Input, the canvas automatically adds an Update tool:

If you click the "File" section in the Update tool's config you can change which section of the query to update, just like you would in the Dynamic Query: