Oracle Number Format in Dremio 23.2.2

1

Hi there,

I have an Oracle column with the data type NUMBER that returns a value of 0.0100015. However, Dremio does not recognize this data type and is returning the truncated value as 0.010002.

Could anyone please help me with this issue?

Dremio 23.2.2-202307280452300417-9276e770

image
image1654×514 42.4 KB

image
image783×966 55.2 KB

2

@ericathm Let us start with the push down that is sent to Oracle, can you please provide us with the job profile that will tell us the pushdown, meanwhile, can you please try external query and see if that brings the right value?

3

Hello @balaji.ramaswamy , thanks for the help

i tried to use an external query , without transformations :

SELECT VAL_PRECO_UNITARIO FROM table(BDH.external_query('SELECT VAL_PRECO_UNITARIO as VAL_PRECO_UNITARIO FROM CETIPH.OPERACAO where NUM_ID_OPERACAO_H = 6887275048'));

and watching the result, i realized that Dremio always convert the field to DECIMAL (38,6)
Dremio doesn’t return the result with all precisions , same as the source

image
image952×276 17.3 KB

image
image1768×732 70.7 KB

BUT, when I try to convert the field to precision 8, it returns correctly
However, the table has a large volume and is not a possible alternative for us to use it like this
Because, for this conversion, all the volume of the table will be taken to Dremio, instead of being processed in Oracle

SELECT VAL_PRECO_UNITARIO FROM table(BDH.external_query('SELECT cast(VAL_PRECO_UNITARIO as decimal(38,8)) as VAL_PRECO_UNITARIO FROM CETIPH.OPERACAO where NUM_ID_OPERACAO_H = 6887275048'));

image
image1239×238 17.6 KB

image
image1743×598 61.7 KB

Is there any internal configuration in Dremio to remove the forced conversion of Decimal (38,6) when the data type is number in Oracle?

thanks a lot

4

@ericathm It is hard to understand from the screenshot, ca you please send us the profile?

5

@balaji.ramaswamy follow the attachments, thanks

normal.zip (12,1,KB)

convert.zip (12,2,KB)

6

@ericathm Having trouble opening the normal profile. Let us do this, is it possibleto send the “CREATE TABLE” DDL for BASEH.OPERACAO from SQL Developer or SQL Plus. Best is to recreate the issue so we can find a workaround or solution

7

@balaji.ramaswamy of course

CREATE TABLE "BASEH"."OPERACAO" 
   (    "NUM_ID_OPERACAO_H" NUMBER, 
    "NUM_OPERACAO_PARTE" VARCHAR2(10 BYTE), 
    "NUM_OPERACAO_CPARTE" VARCHAR2(10 BYTE), 
    "COD_OPERACAO" VARCHAR2(16 BYTE), 
    "DAT_OPERACAO" DATE, 
    "QTD_OPERACAO" NUMBER, 
    "VAL_PRECO_UNITARIO" NUMBER)
8

@ericathm Thanks, let me get back on this