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 OCTOBER 22, 2024

Applies to:

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

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.