SQL function CAST does not appear to work in SQL queries

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!

Datasets

Developer Tools

Input

Error Message

Common Use Cases

Dynamic Processing

Accepted answers

All comments

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,

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.

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.

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.

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.

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703