Division operation in Netezza when using external data
Article ID: KB0081360
Updated On:
| Products | Versions |
|---|---|
| Spotfire Analyst | All Versions |
Description
When using data from a Netezza Data Connection which is kept as External Data table, the division results may appear incorrect. For example, you want to perform division between two columns like:
Count([Col1]) will be integer whereas Sum([Col2]) will be real as data type if Col2 column is real. So whereas Sum([Col1])/Sum([Col2]) will give correct results as data type of result is Real, an expression like Count([Col1])/Count( [Col2]) will not return correct result in decimals as data type of result is Integer.
Count([Col1])/Sum([Col2])But the above result always returns the nearest whole number even though you may have changed the formatting to include 2 decimal places. For example: 3/4 will show 0, 5/4 will show 1 etc.
Count([Col1]) will be integer whereas Sum([Col2]) will be real as data type if Col2 column is real. So whereas Sum([Col1])/Sum([Col2]) will give correct results as data type of result is Real, an expression like Count([Col1])/Count( [Col2]) will not return correct result in decimals as data type of result is Integer.
Issue/Introduction
This article shows how to divide integer columns and get result in decimals when Netezza data connection is kept as external data type
Resolution
This happens because division in Netezza considers the data type of result for numerator and denominator. In order to achieve the desired result in decimals for division, you will need to implicitly convert the numerator and denominator to float data type by multiplying with 1.0. For example:
Count([Col1])*1.0/Count([Col2])*1.0
Additional Information
Feedback
Yes
No
Powered by
