Microsoft KB Archive/219176

= PRB: Division by Zero Displays Results in Strange Format =

Article ID: 219176

Article Last Modified on 2/24/2004

-

APPLIES TO


 * Microsoft SQL Server OLAP Services
 * Microsoft SQL Server 2000 Standard Edition

-



This article was previously published under Q219176



BUG #: 168 (Plato7X)



SYMPTOMS
If a 'division by zero' operation is performed in a MDX query, the results appear in a strange format such as "1.#J". Some examples follow:


 * with member measures.x as '1/0'

select { measures.x } on columns from [bobs video store] returns 1.#INF
 * with member measures.x as '1/0', FORMAT_STRING = ''

select { measures.x } on columns from [bobs video store] returns 1.#INF
 * with member measures.x as '1/0', FORMAT_STRING = 'Standard'

select { measures.x } on columns from [bobs video store] returns 1.#J
 * with member measures.x as '1/0', FORMAT_STRING = 'Fixed'

select { measures.x } on columns from [bobs video store] returns 1.#J
 * with member measures.x as '1/0', FORMAT_STRING = 'Percent'

select { measures.x } on columns from [bobs video store] returns 1#I.NF%
 * with member measures.x as '1/0', FORMAT_STRING = 'Scientific'

select { measures.x } on columns from [bobs video store] returns 1.#JE+00

The following table shows query return values from using different FORMAT_STRING's in an expression involving 'division by zero' (tested on Intel platforms):



WORKAROUND
In general, avoid division by zero whenever possible. Inside a calculated member, use explicit checking before dividing. For example, instead of the expression 'Sales / Units', use the function 'iif' such as 'iif ( Units = 0, 0, Sales / Units )'.

NOTE: The explicit checking may not always work. It will work for integer values, but may fail for real values.



MORE INFORMATION
This functionality is provided by the Oleaut32.dll file. This is how the OLE Automation function formats numbers. The same behavior can be seen in Visual Basic's Format function, or in any product using OLE Automation.

Keywords: kbprb KB219176

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.