Using User Constant in Input Data Tool - SQL Editor

[hjcho928]

Hi Experts,

Total newbie here with Alteryx - I'm trying to import data from an Oracle database and using the SQL Editor of the Input Data tool.

It runs fine by specifying the location in the SQL WHERE clause (example LOCATION = 'CA') in the SQL statement but with the full statement being several hundred lines and referencing the location multiple times, I want to be define (and change when needed) the location once and have SQL reference that for the location in the WHERE clause.

I came across using User Constants as parameter within the query for this type of situation, but for some reason it's not working. 

I initially had "Standard Workflow" selected as the Workflow type but came across another discussion here where essentially the same issue as mine was resolved by changing the workflow type to "Analytic App".  Selecting that type doesn't seem to work for me, however. 

Testing the Query referencing User Constant in the SQL Editor shows as a successful connection but actually running it results in no data, unlike when I specified 'CA" above, that gives actual output.

[DavidSkaife · Accepted solution]

Hi @hjcho928 

You're missing a bit in the reference, change it to '%User.VLOCATION%' and see if that works.

EDIT: Just tested this myself and i didn't need to make the workflow an Analytical App, so suggest trying it as a normal workflow first with the updated reference. It might be worth reading up on Apps anyway, as they might serve your purpose more depending on how you want to use the workflow as an end product - https://community.alteryx.com/t5/Engine-Works/Alteryx-Analytic-Apps-Introduction-and-Deep-Dive-Serie... https://community.alteryx.com/t5/Interactive-Lessons/tkb-p/interactive-lessons/label-name/Creating%2...

[hjcho928]

Thank you!! Worked like a charm 👍

[jmcconnell]

I have been struggling with this now for the last day.  My situation is slightly different as I'm trying to do this within a Batch Macro but I don't know why that would mean I can't reference a Workflow User Constant in the SQL Editor of an Input Control.  This was working fine with values hardcoded into my SQL query but as soon as I try to use the User constant I my query is no longer valid.