Can't publish workflow with in-db tool using custom SQL

[catherine_desesa]

I need to push an application to the gallery but becasue my entire app is a SQL statement, in a single connect in-db tool, and the SQL editor can't parse my code (my code that runs just fine!), I cannot save my app to the Gallery.  How can I turn the SQL checker OFF ?

[rdoptis]

Hi Catherine,

Im not aware of a way to disable query validation, but I have seen similar errors when using shorthand. For instance there is a lot of Teradata specific shorthand such as using "Sel" instead of "Select", and when using this non ANSI conforming shorthand the query validator complains (even when the actual DB would have no issue with the SQL).  Maybe something along these lines could be causing your issue? 

Best,

Ryan

[chris_love]

Hey C,

Since you're using an app why not use a simple dummy SQL taht parses and then have an action (no question) that swaps in the custom SQL at runtime?

It's a workaround but it'll work

Chris

[jklimek]

Hi Chris,

I work on Cate's team and we are working on this issue together.  I'm not sure I completely follow your suggestion, but would love to hop on a call when you have a chance to show you the workflow.  Please send me a message if you are available.

Here is what I tried based on your suggestion:  Connected an action tool to the input tool, selected action type as "Update Value with Formula", pasted the full SQL query we are trying to execute with the parameters in the "formula" expression builder.  I then get an error about the expression because this is not a normal Alteryx expression.

Just to give you a little more background on the app, we have a long SQL query currently in an input tool.  We then have parameters/strings that are updated/replaced based on the interface tools.

Thanks for your help!

-John    

[chris_love]

I can't jump on a call but wrap the whole SQL query in double quotes and you should be okay. If there are double quotes in your SQL query then you'll need to double them up.

e.g. [Column] = "Chris" becomes "[Column] = ""Chris"""

[BenG · Accepted solution]

I was able to get this to work by reading the query in from a file, using a find and replace that is updated by interface tools, and then feeding into a dynamic replace.  I was then able to upload to the gallery and run.  the attached workflow demonstrates a simple test of this.

[catherine_desesa · Accepted solution]

Carlos came up with the best solution - drop the tool into a container, disable the container, but set the default to enabled.  Then save to the gallery and run as usual.

Thanks Carlos and everyone for your kind replies!