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