PRB: Division by Zero Displays Results in Strange Format

ID: Q219176

The information in this article applies to:

Microsoft SQL Server OLAP Services version 7.0

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):

Format Strings	Query Return Values
FORMAT_STRING="	1.#INF
FORMAT_STRING='Standard'	1.#J
FORMAT_STRING='Fixed'	1.#J
FORMAT_STRING='Percent'	1#I.NF%
FORMAT_STRING='Scientific'	1.JE+00

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.

Additional query words:


Keywords          : SSOSmxd kbSQLServ700 
Version           : winnt:7.0
Platform          : winnt 
Issue type        : kbprb

Last Reviewed: June 4, 1999