In-DB variable type change

[Ehatie]

Hello community,

Has anyone ever succesfully changed the variable type using the Formula In-DB for a specific variable?

I've tried a multitude of SQL syntax schemas and non seems to work, can't believe something so simple is so complicated in Alteryx and is taking me so long to achieve, please be a ray of hope and shine light on how to operate the correct syntax to achieve this.

Regards,

[MarqueeCrew · Accepted solution]

@Ehatie,

What is simple in Alteryx, is simple because of it's thoughtful design.  The data can be altered from one data type to another because the source data is not altered.  That being said, if you were to create a new variable, then you could create it in any type that suits you.  If your data is going elsewhere, you can create a new variable, such as "ID_Copy" and once you have it in your new format, you could place a select (IN DB) tool onto the canvas and you could "De-Select" the ID field and "Rename" the "ID_Copy" as "ID" (note:  you may need two separate select tools to accomplish this).  Now ID can be alphanumeric coming out of the transformation process.

If you truly want to change the data type of the source data, I would contact your DBA.  There are ALTER commands in SQL that will change the data type of the table columns.  I really don't think that this is what you are trying to accomplish.

Cheers,

Mark

[RodL]

To tag on to @MarqueeCrew's comment, Alteryx isn't meant to be a "database management" platform...it's about getting data easily in the hands of people who need to do analytics. From that perspective the In-DB tools were added to the platform a couple of years ago so that the people who are doing the analytics (but not necessarily SQL coders) could leverage database platforms and have processes run in-DB rather than pull all of the data into memory in Alteryx. They can do this with the drag-and-drop In-DB tools that provides the Alteryx user an easy-to-use interface that they have come to expect, but without needing to know how to code.

That said, if you do happen to know how to code SQL, what you can just as easily do is create a query that will cast a field in the table to another data type and merely copy/paste this query into a regular Input Data tool. Because there is a specific set of "in-DB" tools in Alteryx, some users assume that's the only place that in-DB processes can occur, and don't realize that a query in the "standard" Input Data tool is really running "in-DB" as well and leveraging the server side process.

I've seen some fairly complex SQL queries inside an Input Data tool where users have wanted to have an in-DB process and have achieved it in that way without the "In-DB" tools.

Hope this helps with the context...