Whenever I use the Dynamic Input tool -- or really any tool that modifies a SQL query, like some of the Interface tools -- I constantly get tripped up by Alteryx's tendency to change the case and spacing of my SQL queries.
For example, if I write a SQL query with the following WHERE clause:
WHERE LEFT([String],5) IN ('ABCDE','FGHIJ')
Even if I copy and paste that exact text into the SQL Editor, Alteryx will make slight changes. The new text will look something like:
where Left([String], 5) in ('ABCDE', 'FGHIJ')
The changes are: 1.) WHERE changed to where, 2.) LEFT changed to Left, 3.) space added between [String] and 5, 4.) IN changed to in, and 5.) space added between 'ABCDE' and 'FGHIJ'
Then, in the Configuration Window, when I choose "Modify SQL Query --> Replace a Specific String," if I enter the SQL that I originally wrote, Alteryx won't recognize it since the capitalization/case and spaces are slightly different than what's contained in the SQL Editor. I have to re-open the SQL Editor, copy the string of interest (i.e. the string that Alteryx changed the case of / added spaces to), and paste it into the "Replace a Specific String" window.
Has anyone figured out a way to not get tripped up by this? Is there a way to tell Alteryx to stop changing the case and/or stop adding spaces to my SQL queries?
If not, is there a way to suggest to Alteryx that the SQL Editor ingest the text exactly as entered, as opposed to tweaking the case and spacing?
@mnmemilymnm
I have given a few tries, not with the same query you are using though.
It appears that the bahaviror is somehow unpredicitive...😁
Based on the test, if we modify the query in the Query Editor, it will somehow retain the formatting, casing.