Dynamic SQL in Alteryx application that obeys parameters entered

I appreciate your help in the following inconvenience.
What happens is that I have the following flow, as you can see I have a several input parameters.

In the "Action" component I have a replace in which I say whatever comes in the parameter value modify me in the script that I have connecting to a database.

If we can visualize in the script I have in the where a fixed parameter and with the replace I am telling him to replace me with the parameter that comes.

The problem I have is that sometimes there are times where in the application can send me or not the values, that for this I need the condition to disappear and in the where for this case the provider would catch all, but for a performance issue I do not want in the where I put all the values but simply that condition would not go and I will consult the database all values.

Please help with this inconvenience.

Thank you in advance.

Developer Tools

Settings

Error Message

Transformation

Apps

Dynamic Processing

Database Connection

Iterative Macro

Accepted answers

JessicaS

Hi @karina_mazon,

I've provided a general example of the approach that I would take to modify a sql query dynamically for use in the indb tools.

I would use action tool(s) to update text input(s) containing each parameter for the query.

Then I would leverage the other tools in alteryx to compile my parameters into a query. Note that I wrote the query as an if statement to cover the use case where a user inputs no values into the app.

Finally, I would use dynamic input indb to run the query I've created with my workflow.

There are many many ways to do a dynamic query in alteryx but I find this approach easier to troubleshoot (especially as a non - SQL expert) vs using a single action tool on a very complex formula to update query. Hopefully others can share their approach as well.

Thanks,

App_UpdateINDBQUERY.yxwz

All comments

Claje

Hi,

If you modify your query slightly i think this is relatively easy to do:

If you change the WHERE clause to start as:

WHERE 1=1 AND l.COD_PROVEEDOR = '5243'

That will make it so that you don't have to worry about what the "first" part of the where clause is.

Then in your Action tool you can modify the Proveedor piece of the update to something like :

IF ISEMPTY([ProveedorQuestion]) THEN REPLACE([Destination],"AND l.COD_PROVEEDOR = '5243'","")

This should allow you to completely skip this condition if there is no value entered

karina_mazon

Thanks for responding in advance I appreciate your help.

I commented that I followed your recommendation but I have some problems. Specifically I am doing the example with the I.COD_CLASSIFICATION where the parameter input screen is a list box, as a way to verify that the process is correctly done I have exported a csv file with the values of the classifications, at the moment of putting the sentence and not to mark any value I appear the codes of classification empty. Maybe it could be because of a List Box theme? or in turn some additional configuration of the entry of the List Box?

I appreciate your kind help.

karina_mazon

Hello how are you,

I tell you that I came out the subject that when it is null I remove the condition of the where and therefore I will bring all the data.


Now I wanted to ask your collaboration with a problem that I can not solve, the question is that this process I must perform for 8 input parameters, 
but the question is that as I do not lose the value of the other; ie the user can send nulls in classifications as in the input providers, but I do 
not know how to make the condition for all entries, my idea was to put it in a temporary variable to go modify it, but the truth is not how to do it, 
the truth I do not want to make several if combinations because as I said, they are about 8 entries.

Thank you in advance for your support.

Quick Links

This months top contributors

mceleavey 383

mbarone 337

Hollingsworth 335

LanisC 335

RithiS 280