Explicit and implicit Netezza Type Casting With Examples

Netezza type casting is converting the value with one data type to other. There are two type of type casting: Implicit and Explicit type casting.

Implicit Netezza Type Casting

In an implicit type casting Netezza uses internal rules and try to evaluate the process statements that contain the mixed data types. This will be very helpful when using a function that expects a particular data type, or when issuing a query that compares values that are similar but of different data types.

For example, Consider the below query that compare two expressions.

SELECT * FROM table 
WHERE <character expression> > <integer expression>;

Where expressions could be column names or more complex expressions.

Since a character expression is being compared to an integer expression, the character expression is implicitly cast as an integer. Here no manual intervene is required as the Netezza handles everything internally.

Read:

• Netezza nzsql Command and its Usage
• Netezza Conversion Functions and Examples

Implicit Netezza Type Casting Failure

Sometimes implicit Netezza type casting fails because Netezza cannot parse data to a required data types. For examples, character ‘aaa’ cannot be parsed as an integer.

Error messages that are issued when implicit casting fails:

When Netezza cannot parse an expression as an integer:

ERROR: pg_atoi: error in "abc": can’t parse "abc"

When Netezza cannot parse a character expression as a date value:

ERROR: Bad date external representation ’abc’

• Read: How to Resolve Netezza SPU Swap Partition Error

Explicit Netezza Type Casting

Explicit type casting is converting the values of one data type to other with the help of “CAST” function. Use the cast function to explicitly cast the data to the appropriate data type.

For example, Consider if the column ID contains character data that represents integer values, cast that data as integer data like this:

SELECT * FROM table WHERE cast(ID as integer) > 3;

Examples of Netezza Type Casting

Convert decimal to integer use cast( expression/column as int):

select cast(456456.30 as int);

Convert integer to decimal use cast(expression/column as numeric(precision,scale)) or cast(value as decimal(precision,scale)):

select cast(1234 as numeric(7,2))

Convert date to specific character format use to_char function 

select to_char(current_date,'YYYYMMDD')

• Read: Netezza Date functions and Examples

Convert character values containing date to date format, use to_date function

select to_date(value,'YYYYMMDD')

convert string to number use to_number function

select to_number('12345','99999')