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!

[mceleavey]

Hi @FreeRangeDingo ,

I don't know what a Dynamic Query tool is, so I'm confused. Can you provide some screenshots, or even the workflow so we can see what you're trying to do?

Thanks,

M.

[Maskell_Rascal]

Hi @FreeRangeDingo 

I'm guessing you are using the In-DB tools, hence the need to a SQL query. Cast works in Alteryx provided it is a function available within your SQL server. You would query the column with the table as follows:

select cast(cast(jointventure as bigint) as string) as JV_Trimmed 
from YourTable

I tested this using a databricks connection in Alteryx within the Connect In-DB tool, and the query came back as expected.

Let me know if this works for you.

Cheers!

Phil

[dYoast]

CAST worked for me in a dynamic input

[FreeRangeDingo]

@mceleavey I said Dynamic Query.  I should have said Dynamic Input.  It's in the Developer pallete.  It allows you to replace strings or where clauses.

[FreeRangeDingo]

@Maskell_Rascal I am not using in database tools.  I am using Dynamic Input.

[FreeRangeDingo]

@dYoast That is exactly what I was trying to do, and what you show is what I did.  I also tested that the query worked and that there wasn't some other part of it that was causing issues.  Is there possibly a setting somewhere to enable SQL functions???? I am having a similiar problem with GETDATE()...similiar in that it's a simple function that doesn't work.

[mceleavey]

Hi @FreeRangeDingo ,

in that case, why are you using a CAST function? Is there any need? You're not using In-DB, so there doesn't appear to be any point to using a SQL query at all. Can you give us some more information around what you're trying to do? Also, can you provide screenshots? Show us the error etc.

M.

[Maskell_Rascal]

I agree with @mceleavey, we need to know more about what error is being thrown. I have some suspicions on what could be a potential problem, but without knowing more I'm flying blind. 

For example, the snippet of code you provided should not have parenthesis or a space in it to be used in a SQL query. It should look like this:

CAST(jointventure as INT) as JVTrimmed

[FreeRangeDingo]

@Maskell_Rascal you have to have parenthesis around the cast and Alteryx requires you to use quotes around the column name when there is a space.  What you showed is what I put into Alteryx. I tried changing the name to something without a space and not using any quotes.  It seems like Alteryx isn't recognizing any SQL functions.