SQL function CAST does not appear to work in SQL queries

[FreeRangeDingo]

I am writing a fairly simple SQL query that sits in a Dynamic Query tool.  The column that I need to by "dynamic" is called the JV number.  The JV number has leading zeros in it that need to be stripped out in order for the dynamic query to work.  JV number comes in as a string.  Ultimately, I want to convert it to an integer (to strip the leading zeos) and then back to a string.  I have tried both cast and convert, and both of these return "No Columns Returned".  

CAST(jointventure as INT) as "JV Trimmed"

Convert(jointventure) as "JV Trimmed"

Please note, I know how to perform this function with a formula tool using TrimLeft, but I need the leading zeros to be removed in the SQL query.  

Does CAST not work in Alteryx or is there something I am doing wrong?

Thanks in advance!

[Maskell_Rascal]

@FreeRangeDingo Sorry, I meant quotes not parenthesis. That being said, unless you share more info on the error being thrown (screenshots, a sample workflow, etc.) there really isn't anything we can do for you. 

[FreeRangeDingo]

@mceleavey  I am writing a SQL query that has a where clause for the JV Number and the Owner Number.  These numbers coming thru the workflow will change, so I use a Dynamic Input tool.  It's similar to using an Input tool with a SQL query but you add clauses like the one shown to replace a value in the Where clause. 

I have a where clause that says where Owner Number = '1234'.  That will be replaced by the value in a column called Owner Number.

The problem is that the number can have any number of leading zeros, and what gets fed into the Owner Number column does NOT have leading zeros.  I am trying to use CAST to cast my string to an integer to remove the leading zeros and then cast back to a string so that it will match what is in the Owner Number column.  I was trying to get thru the first part, cast to int, which didn't work.  

The error is just "No Columns Returned".  Additionally, you know how you can toggle back and forth between SQL Editor and the Visual Query Builder, and when you go to the Visual Query Builder, Alteryx will give you a specific error message (ex. Unexpected character at line 11, pos 3). I don't get any error messages there.  Just when I attempt to run the query, the error is "No Columns Returned". 

[mceleavey]

@FreeRangeDingo ,

ok, I get the problem, so WHICH number has any number of leading zeros, the one you're feeding in from the column in your workflow or the numbers in the SQL table?

This would be a LOT easier if you would show us!

If the numbers coming in from your workflow have leading zeros, simply use a select tool (or formula tool to create a new version of the column if the original is required) to change the data type to numeric before feeding it into the dynamic input tool, this will remove the leading zeros and therefore match to the number in the SQL table.

M.

[dYoast]

@FreeRangeDingo  - Does your query work in a normal input tool?

I would get it to work in a normal input first and then convert to a dynamic input.

[mceleavey]

@dYoast  and @FreeRangeDingo ,  if that works you could just wrap the input in a macro and attach a control parameter, then simply feed in the numbers as the batch to over-write the value in the where clause.

M.

[FreeRangeDingo]

Figure it out.  It was dumb.  The data source was Denodo, which I equate to SQL, but it is in fact different. Once I stopped googling SQL and put in the right Denodo functions it was fine.