community
cancel
Showing results for 
Search instead for 
Did you mean: 

Alteryx designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.

Dynamic input and "Modify SQL Query" options

Atom

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?

Alteryx
Alteryx

I think the easier option to changing your SQL query is to use the "Replace a Specific String" option rather than "SQL: Update WHERE clause".

 

Just make the text to replace 20120101 and select the field where you have your correctly formatted date data.

Adam Riley
Principal Software Engineer
Tech Lead Core Engines, Alteryx
Meteor

Hi Adam,

 

Thanks for posting. I was able to use what you provided, however hoping you can help..

I have a sumarize tool that comes out of 1 QRY that I am trying to have feed the dyanmic input

to modifiy two WHERE IN clasues... and i get the error "only one action may specificy group for SQL IN"

 

any thoughts?

 

QRY is basically...

Select

My_Fields

from

My_table

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'

 

 

Thanks,

 

Mallory

ACE Emeritus
ACE Emeritus

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.

 

Select

My_Fields

from

My_table

where 1=0

 

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

Meteoroid

Can you please provide a workbook example using a batch macro and control paramter?

ACE Emeritus
ACE Emeritus

Will do sir! Give me a few moments and I will put together a simplified version, along with an explanation of how I would use it in general :-)

Atom

I'd love a copy of that example as well! Thanks!

Highlighted
ACE Emeritus
ACE Emeritus

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...  

 

Does that help? :-)

 

 

Meteor

This is by far the best suggestion.  Replace the Date that was bolded with something like "XXX" and then run a dynamic query and replace the XXXX string -- works like a charm

Meteoroid

Let's say I have a where statement similar to the below:

Select * FROM table1
where tradedate between 1/1/2016 and 6/14/2016 and currency=usd

how can I update only one of the date fields?

 

Labels