Netezza / PureData – SQL Cast Conversion to Integers
From time to time, I have had to look up this information, especially, when working with old school SQL intensive ETL patterns where the transformations are being performed in SQL rather than within the ETL application. So, rather than scrambling to find the differing integer casting range limits, bit type conversions, and alias for different integer types, and pieces it together, I thought I would consolidate it here for future reference.
Cast Conversion Format
There are some important rules in the summary of Netezza casting reference below, but here are a few, which I have found useful to remember:
It is safer to convert from a small to a larger integer type, otherwise, the input values may not fit
You may need to apply other data treatments to handle special characters, and decimals to prevent casting errors.
Basic Casting Conversion Format
cast(<<FieldName>> as <<IntegerType_or_Alias>>) as <<FieldName>>
Example Casting Integers SQL
Select
cast(127 as BYTEINT ) as BYTEINT_8_Bit,
cast(127 as Int1) as BYTEINT_ALIAS,
cast(32767 as SMALLINT ) as SMALLINT_16_Bit,
cast(32767 as Int ) as SMALLINT_ALIAS,
cast(2147483647 as INTEGER ) as INTEGER_32_Bit,
cast(2147483647 as Int) as INTEGER_ALIAS_1,
cast(2147483647 as Int4 ) as INTEGER_ALIAS_2,
cast(9223372036854775807 as BIGINT ) as BIGINT_64_bit,
cast(9223372036854775807 as Int8 ) as BIGINT_ALIAS_1
From _v_dual;
Table Of Netezza Integer Types
Type
Alias Names
Bit Size
Value Range
BYTEINT
INT1
8-bit
–128 to 127
SMALLINT
INT2
16-bit
–32,768 to 32,767
INTEGER
INT, INT4
32-bit
–2,147,483,648 to 2,147,483,647
BIGINT
INT8
64-bit
-9,223,372,036,854,775,808 to 9,223,372,036,854,775,807
IBM, IBM Knowledge Center, PureData System for Analytics, Version 7.2.1
IBM Netezza stored procedures, NZPLSQL statements and grammar, Variables and constants, Data types and aliases
