This site uses different types of cookies, including analytics and functional cookies (its own and from other sites). To change your cookie settings or find out more, click here. If you continue browsing our website, you accept these cookies.
I'm using Dynamic Input tool to fetch incremental data from a database. I have a hard time figuring out how Alteryx decides which 'Where' clauses can be modified by the dynamic input and which can not. Basically I have a WHERE clause with a constant that I can not modify, because that rule does not appear on the drop-down list at all.
My sample SQL is as follows:
SELECT lt.counterpart_su_key ,h.org_su_key as hr_org_su_key FROM sp.lt_trades lt LEFT OUTER JOIN glostatic.gs_hr_person_org h on (lt.person_su_key = lt.person_su_key AND h.default_hr = 'Y') WHERE 1 = 1 AND to_char(lt.created_date,'yyyymmdd') > '20120101'
My assumption would be that I could modify the date string '20120101' (bolded above), but I can not.
The only choice I have from the dropdown is to modify the join rule h.default_hr = 'Y' (also bolded above).
It seems to me that Alteryx considers only those where clauses of the form field <operator> constant modifiable.
If there are any calculations based on the field, then that can not be modified, even though the constant is on the other side of the operator.
One can get around this by wrapping the whole SQL (including the calculations) inside another select, but I'm wondering if there are any better solutions out there?
So another way you could handle this entire problem would be to put your query into a batch macro, and then simply uses a control paramater to pass in your entire where clause string.
Then connect an action tool from your control paramater to your query and use the option "replace a specific Text". You would then replace whatever your where clause is hard-coded in your sql query.
For example: I would hard-code the sql query as below.
and then I would pass in the ENTIRE where clause of "Where field_one IN ( dyanmic results from my summarize tool from another qry i want to put here) and field_two IN (dyanmic Results from another field in the same summarize tool) and date_field > 'I would also like to updated this as well'
Then in your UPDATE option, say "replace the specific string Where 1=0" with whatever your dynamic where clause string is....
I have done this on numerous occasions, and it allows you to pass whatever you want into your where clause. All you need to do is dynamically build that where clause string in a formula tool before passing it into the batch Macro.
I could supply a specific workflow example for you if you need
Here is a very simplified version of what I am talking about. Note: I do not have a valid connection in the database connection, so you will have to change the OLEDB settings to point to whatever server / database you want to actually connect too. however this shouldn't matter, as the point you are looking at is the dynamic changing of the query.
if you look at the query, I am simply selecting all records from a table where 1=0.
select * from sampledloans where 1=0
By default this will return nothing, however I then change the where clause of the query by using an update value to update where "1=0" This control paramater will expect a corretly formatted where clause to be passed into it, so in my outer workflow I pass in a where clause that I hardcoded.
if you run the outer workflow the first browse will show that the where clause I am building is equal too. LoanServicer in ('SLS','Brandon') and LoanAge>10 and Datamonth='6/30/2016'
In my query, the 1=0 is being replaced by this string, and the actual query getting passed into sql in my example is...
select * from sampledloans where LoanServicer in ('SLS','Brandon') and LoanAge>10 and Datamonth='6/30/2016'
The table in my database has the three columns "loanServicer", "loanAge", and "datamonth" so the above query would return all rows where this condition is met.
You can do whatever you like on the outer workflow to dynamically build this string that you pass into the query.... In the past I have made each of the columns be a selection from a drop down list box, or a check box, etc...