Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.

SQL function CAST does not appear to work in SQL queries

FreeRangeDingo
11 - Bolide
11 - Bolide

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!

15 REPLIES 15
mceleavey
17 - Castor
17 - Castor

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.



Bulien

Maskell_Rascal
13 - Pulsar

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
11 - Bolide

CAST worked for me in a dynamic input

 

dYoast_0-1635883144956.png

 

FreeRangeDingo
11 - Bolide
11 - Bolide

@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
11 - Bolide
11 - Bolide

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

FreeRangeDingo
11 - Bolide
11 - Bolide

@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
17 - Castor
17 - Castor

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.



Bulien

Maskell_Rascal
13 - Pulsar

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
11 - Bolide
11 - Bolide

@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.

Labels