I want to construct a SQL query changing the “order by” and the “where” clausule.
I notice that I can define some parameters as variable, but not the sql tags.
For instance, I can use a variable to define the column for the “order by” tag, like:
select code as “Code”, val as “Value”, desc as “description”
order by {_SortColVar} ASC
But this has some limitation, for instance, if there isn’t any order defined or if the order is DESC
I resolved this problem with if steps, but I am not sure if I can do the same with the where clausule.
It will be easier to build the SQL query in a variable and, at the end, execute the SQL query that is in the variable.
Is this possible?
you can execute the select as usual in a Database Connector.
To clarify a bit how Flow Variables can be used in the Database conenctor (which i assume you are using here?).
you can use 2 ways of writting variables with different effects:
Version1:
where order_no = :MyVariable
If you use this logic Flow will automatically recognize what input the field Order_no requires and convert the SQL for you. It will do that in general for all variables used by this syntax. This is the recommended variable usage, but it’s limited to use cases like e.g. above.
Version 2:
where order_no = ‘{MyVariable}’
As you can see in this example i need to add the ‘‘ around it to identify it as a string. The fundamental difference here is, that using this syntax flow will fully replace the variable name with its content one to one before executing anything on the database. So anything you write in the variable is what get’s replaced in the code string before it’s executed. The danger with this is that you would need to make sure your input for the variables cannot be manipulated like e.g. someone writting a “order by xxx; Drop Table xx; delete * from… “ This is why this usage is not recommended unless you are 100% sure you can exclude manipulated input. Otherwise the danger of a malicious SQL insert is always there.
For your questions it means you can delete the hardcoded ASC and either create a second variable that contains ASC or DESC (based on your need) and add it after your first variable OR you can put both infos into the same variable like “code ASC”.
rereading your question it’s also possible to write the (almost) full Sql in a variable but again with some restrictions. Flow needs to know the output so you will have to at least define that. It also means that that output needs to remain constant. So you cannot add or remove columns in the logic for the SQL.
An Example:
Generic Select in variable MyTable:
“select code , val, desc from xx
order by Code ASC”
You can then write a select like:
select code "Code” , val “Value”, desc “description” from {myTbl} alias”
This way flow would know the output but you can still have a fully generic select. Not sure when you would use this instead of above option though
yes as Johannes said, whole sql code can go in a variable and can be used with {} in machine steps.
The problem is Flow realizing what you selected while you build your app (you can simply select {Sql} and it will fetch it but due to Flow validation you wont be able to use it down the line)
So what i did is “initialize” the variables in the same machine step and then UNION ALL it to my constructed Sql and then just removed that init row in assignment step:
