Support for unsigned int database type?

[jgreene]

Does Alteryx support the unsigned int database type? I'm seeing some strange behavior for unsigned ints above the 2147483647 limit for a signed int and I don't see any way to force an unsigned data type in the UI.

[ChadM]

What is the strange behavior?  Could you describe it and post any system messages or errors?

Thanks! 

[jgreene]

It looks like it was interpreting the unsigned int from the database as signed once it loaded into Alteryx, so record id's above the 2.1b signed limit were showing as negative after processing through Alteryx.

[ChadM]

Hmm, could you try adding a Select Tool just after your Input Tool, then change that value from an Int32 to Int64?  

[jgreene]

I tried that but it retained the negative values.

[Ned]

If you use a select tool to convert to int64 and then follow up with a formula:

if [Value]<0 then 4294967296+[Value] else [Value] endif

should fix up your negative values.  This is from memory, I have not tested, so make sure you test 1st.

[SeanAdams]

Hey @jgreene

Did this get you to an answer that solved your problem? If yes, would you mind marking this one as solved (to include this in the knowledge base) - or if there are still open questions, feel free to reply, and we can work together to find an answer that gets you where you need to be.

Thanks @jgreene, have a good weekend
Sean

[jgreene]

I didn't ever test the workaround as we changed the type in the database nor have I tested the most recent versions of Alteryx to see if this bug has been fixed.

[SeanAdams · Accepted solution]

Hi @jgreene,

I've tried to simulate this on my home infra - I have a SQL server installed at home - but unfortunately MS SQL doesn't support unsigned integers (this seems to be a long-standing debate in the MS SQL team - see here https://connect.microsoft.com/SQLServer/feedback/details/515502/unsigned-integer-data-type)

However my understanding is:

- This is not natively supported by Alteryx (although I may be wrong, but I cannot find any reference to this, and based on @Ned 's response, I'd guess it's not part of the core DB engine)

- this is supported by ODBC natively (https://docs.microsoft.com/en-us/sql/odbc/reference/appendixes/sql-data-types) as long as the DB driver supports it.

So - two steps here:

a) workaround that works - on my own server, I've experimented with two options, namely bring integers (in your case, only unsigned integers) back either as a larger signed int type or as a string.   Both will work natively in Alteryx

The SQL to do this on a Microsoft SQL server is below this, and this can be put directly into your alteryx input tool.   The query syntax may not be the same on your database, but if you let me know what DBMS you're using, I'm happy to help you to find the equivalent SQL for your server.

SQL: 

Select
               cast(TestTable.testInt as varchar(10)) as TestIntAsString,
               cast(TestTable.testInt as BigInt) as TestIntAsBigInt
 From TestTable

b) Second thing to do is to log this as an idea in the ideas section - I'll do this right now, and give you credit for it.

Based on this - hopefully we can mark this thread as closed - and focus on the idea entry to add native support for this in a future release.

Cheers @jgreene

Sean