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

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-Series-Part-1/ba-p/669655 https://community.alteryx.com/t5/Interactive-Lessons/tkb-p/interactive-lessons/label-name/Creating%20Analytic%20Apps