If you are working on the different data sources and trying to load data to Netezza from multiple sources than you might encounter issues such as characters in integer column and that leads the loading to fail. Hence, it is very important to perform Netezza integer value check when you are not sure about source data types.
Why should check for bad Records?
If you are working on the data warehouse and multiple sources, sometimes you load in data sourcing from character data into a numeric or integer field in the Netezza table. The loading or the entire SQL script will fail with error atoi (alpha to integer error). To avoid such a situation you really need to check the data or remove the bad records so that loading will continue without any issues.
Read:
- Netezza Cumulative Sum, Average and Example
- IBM Netezza Update Join Syntax and Examples
- Netezza Split Delimited Fields into Table Records and Examples
- Quick and Best way to compare two tables in SQL
- Identify and Remove Netezza Duplicate Records in Table
- Search for String Pattern in Netezza Database: Google Like Search
Netezza Integer Value Check Methods
There are multiple methods available to check for integer values in Netezza:
One of the easiest and best methods is actually to use the Netezza SQLToolkit functions and use a regular expression to remove the bad records and load only numeric or integer data.
Netezza Integer Value Check Examples
select regexp_extract(my_source_column,’^[0-9]{1,18}$’)
from your_source_table;
The above query will return integer value up-to 18 digits.
This will only return an integer of up to 18 digits. You can also use the above login in the WHERE clause of the source table to filter bad records.
Netezza Integer Value Check other Methods
If you have not installed the Netezza SQLToolkit then you will have to go for other options in your hand. In that case, you can use the istrue function along with to_number and try to convert character value to numeric. You can also use the CASE WHEN function in Netezza to substitute the default value in case of bad records.
Netezza Integer Value Check other Methods Examples
Select case when istrue(cast(to_number(sourcecolumn,’999999999999999999′) as varchar(20)) = sourcecolumn) THEN sourcecolumn else 0 end integer columnFrom Your_source_table;
Another Method is to use translate function to check integer value
select case when length(translate('12345134',0123456789,'')) > 0 then 'Not an integer' else 'Integer' end;