OBIEE 11.1.1.7.x:Unable to Get Actual Decimal Value by Executing Logical Query with nqcmd command (Doc ID 2099632.1)

Last updated on MARCH 31, 2016

Applies to:

Business Intelligence Suite Enterprise Edition - Version 11.1.1.7.0 and later
Oracle Exalytics Software - Version 1.0.0.3.0 and later
Business Intelligence Server Enterprise Edition - Version 11.1.1.7.0 and later
Information in this document applies to any platform.

Symptoms

While trying to get actual numeric value from BI Server using nqcmd command, it truncates precision of the value to 2 decimal places.

An example of truncated numeric value is given below:

s_0 s_1 s_2 s_3

---------------------
0 2.00 98.77 12.35

This issue is reproducible with following steps.

1)Using SH Subject area, create following table on back end database

CREATE TABLE "SH"."CHANNELS"
( "CHANNEL_ID" NUMBER NOT NULL ENABLE,
"NUM1" NUMBER,
"NUM2" NUMBER(23,14)
)
and insert record to the table as following.
insert into channels (CHANNEL_ID,NUM1,NUM2) values(2,98.7654321,12.3456789);

2)Import the table to repository of OBIEE. Set data type of physical columns for NUM1 and NUM2 as Numeric.

3)Configure appropriate B&M layer and presentation layer on repository of OBIEE.

4)Create analysis on OBIEE for the table and get following logical SQL.

SELECT
0 s_0,
"SH"."CHANNELS"."CHANNEL_ID" s_1,
"SH"."CHANNELS"."NUM1" s_2,
"SH"."CHANNELS"."NUM2" s_3
FROM "SH"
ORDER BY 1, 2 ASC NULLS LAST, 3 ASC NULLS LAST, 4 ASC NULLS LAST
FETCH FIRST 65001 ROWS ONLY

5) Save the logical sql as file and execute nqcmd with the file as following.
nqcmd -d BI -u weblogic -p welcome1 -s d:\tmp\test.sql

6)Following result is observed. Numeric values are truncated.

s_0 s_1 s_2 s_3
-----------------------
0 2.00 98.77 12.35

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms