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 |
1 | Mbr_Id, ProcedureCD |
2 | Mbr_Id, ProcedureCD, Claim_Nbr |
Expected Output
ID | Mbr_Id | ProcedureCD | Claim_Nbr |
1 | 12346678 | ABCD | |
2 | 12346678 | ABCD | 34895 |
Solved! Go to Solution.
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.
@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.
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).
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).
@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.
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.
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.
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.
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.