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

Passing different fields to SELECT statement in Dynamic Input

monish_chandra
8 - Asteroid

I'm using Dynamic Input to pass column names from a Text input. Seems like Dynamic Input tool doesn't handle different schemas. The column names are passed to a dynamic Input tool and I''m using Replace a specific string to modify the SQL statement.

 

The SQL statement is as shown below

 

 

SELECT 'col_names'
FROM CLAIMS
WHERE MbrId='12346678'

 

 

Using the Replace Specific string, I'm changing the select criteria i.e 'col_names'

 

It gives me the following error

 

Dynamic Input (13): The file "" has a different number of fields than the 1st file in the set and will be skipped

 

Input:

 

ID Select_fields
1Mbr_Id, ProcedureCD
Mbr_Id, ProcedureCD, Claim_Nbr

 

Expected Output

 

ID Mbr_IdProcedureCDClaim_Nbr
1   12346678ABCD 
212346678ABCD34895

 

 

8 REPLIES 8
Claje
14 - Magnetar

Hi,

 

You've correctly identified that the dynamic input tool doesn't handle different schemas.

 

The workaround to this is to encapsulate your Dynamic Input tool inside of a batch macro, and to configure that macro so that its output schema can change by iteration.

 

I've attached a quick example - you'd just need to update this macro to use your query template.

 

Let me know if you have any questions, and take a look at the batch macro input configuration in my attached workflow.

monish_chandra
8 - Asteroid

@Claje Is it possible to give the implementation of the macro? I tried doing like you said, I'm not sure whether I should 'Update Value(default) for Select fields.

 

Captuasre.PNG

Claje
14 - Magnetar

Hi,

 

If you were able to download my workflow, you should be able to right click the macro in it and "Open Macro" to take a look.

 

In case you can't download mine, here's a quick screenshot.  I changed nothing about your Input (besides converting to macro input).

 

claje_dynamicinputbatch.PNG

 

All you should have to do to get that working is modify the dynamic input tool itself so that it has your sql connection/table in it (or copy your working one from the existing workflow and replace mine).

monish_chandra
8 - Asteroid

@Claje Thanks a lot that worked.

 

Is it possible to use event log within the macro. I wanted to capture the error if any of the queries failed. Didn't want the workflow to halt because of one query. 

Claje
14 - Magnetar

That gets a little trickier.  You may have to refactor portions of the code design to accomplish this.

The hard part is that suppressing errors in Alteryx isn't really a thing you can do, so if a tool throws an error, you're somewhat stuck with it.

 

If I were trying to think about how to accomplish this design, I would try to utilize a different code design.  I would leverage the "PreSQL" command to write (and probably update) my query, and would set it up to insert into a temp table on the server.  I would have the SQL statement simply SELECT * FROM that TEMP table, and use a SQL Try/Catch that would Select an error statemnt into your temp table if an error occurred, using a unique column name (EG SQLQUERYERROR).

 

I can try and put together an example of this, but its largely conceptual in my head, so it may take a little longer than my initial example.

monish_chandra
8 - Asteroid

I tried using the PreSQL and encapsulate the main query with a TRY/CATCH block, but it still errors out. eg: A query like below 

 

SELECT MbrId,
FROM CLAIMS
WHERE ProcedureCD = S010' 

This will break the workflow completely because a single quote is missing. I tried to log the SQL error message into the error table, but it never enters the CATCH block.

Claje
14 - Magnetar

Trying to CATCH SQL syntax errors (beyond incorrect/nonexistent column names) is a much bigger challenge.  If you try running that query in SSMS with a TRY/CATCH around it you'll find that it encounters all sorts of syntax issues (and doesn't get to the CATCH block).

 

I would recommend doing some research on methods for checking Dynamic SQL code to find a good way to accomplish this.  At a high level, there is a function called sys.dm_exec_describe_first_result_set in Microsoft SQL Server which may help with some of this evaluation.

monish_chandra
8 - Asteroid

You are right! handling syntax errors are a bigger challenge. As a workaround, I tweaked your earlier idea of using TEMP table. I'm inserting the records into a temp table before the execution of dynamic input(macro) and after the final step of the workflow I'm performing a left join of the TEMP table and the input based on ID - This will give the record which errored out. 

 

I wish Alteryx had a better error handling capability. Not only SQL errors, but errors in general (write errors to database) etc. 

Labels