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

Dynamic SQL in Alteryx application that obeys parameters entered

karina_mazon
6 - Meteoroid

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.

Captura.PNG

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.

 

Captura1.PNG

 

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.

 

Captura2.PNG

 


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.

 

Captura4.PNG

 

 

 

 

 

 

 

 

9 REPLIES 9
Claje
14 - Magnetar

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
6 - Meteoroid

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?

 

Captura.PNG

 

Captura2.PNG

Captura3.PNG

 

I appreciate your kind help.

karina_mazon
6 - Meteoroid
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.
Captura.PNG 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.

 

JessicaS
Alteryx Alumni (Retired)

Hi @karina_mazon,

 

Can you post your workflow to give us a better sense?

 

Thanks,

Jess Silveri
Manager, Technical Account Management | Alteryx
karina_mazon
6 - Meteoroid

Of course.

Thank you in advance.

JessicaS
Alteryx Alumni (Retired)

Hi @karina_mazon,

 

 

From taking a look here it seems it may be easier to use a separate action tool(s) to delete or update based on no user input for a category.

 

Thanks,

 

Jess Silveri
Manager, Technical Account Management | Alteryx
karina_mazon
6 - Meteoroid

Jessica Please it would be possible that you could help me with an example; in advance I thank you, I am a bit stagnant in this subject.

JessicaS
Alteryx Alumni (Retired)

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.

 

2017-10-03_15-12-25.jpg

 

Thanks,

Jess Silveri
Manager, Technical Account Management | Alteryx
karina_mazon
6 - Meteoroid

Thanks, Jessica

Labels