Division operation in Netezza when using external data

Division operation in Netezza when using external data

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])/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. 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

External: Netezza Operators

• https://www.ibm.com/support/knowledgecenter/en/SSULQD_7.2.0/com.ibm.nz.dbu.doc/r_dbuser_functions_expressions_operators.html