Data360 Analyze

Hello, 

Could you tell me if there's a setting or formatting option in Dataverse (3.1.5.2590) that will allow decimal places to remain?

My query returned a decimal result in my sql editor tool, but once used in Dataverse I'm losing decimals.  I've tried casting using to_number, I changed 1 to be 1.0000, and i also tried rounding to 5 decimal places.  But nothing's working, I'm still losing decimal places in output.

Here's the select portion of my query, ROR is the field where I need decimal places.

select effective_date,

entity_id,

account_id,

'M'||to_char(months_between(to_date('2017-06-30', 'yyyy-mm-dd'),effective_date) + 1) as ret_month

--,one_mon_ror

,round(1.00000 + one_mon_ror,5) as ROR

from holding

Thanks, Rob

Hi Robert,

Could you please provide some answers to the questions below:

• What's the DB you are connecting to?
• What driver is being used?
• What's the data type of the field in the database?
• Can you provide screenshot of the results you see in SQL editor compared with Dataverse?

When you say you are losing decimals, does this relate solely to how the data appears in the Data Viewer? The Dataverse Data Viewer does not show the decimal point and subsequent '0' character for an integer value in a double type field.

For instance, using the default data in the Create Data node. The 'IOU' field contains a number of double values that have a zero decimal component (e.g. the values in the 7th - 10th records):

Hi Adrian, 

My job was simply querying db then writing to file.  The decimals are gone in the file too. 

But I applied the code from your example above and confirmed that the value does not have decimals.  That was a really helpful example though, thanks.  Will help me get started with the new Transform node.

To answer your questions:

• What's the DB you are connecting to?  Oracle
• What driver is being used?  ojdbc6.jar / oracle.jdbc.driver.OracleDriver
• What's the data type of the field in the database? NUMBER
• Can you provide screenshot of the results you see in SQL editor compared with Dataverse?  

Robert,

One of the Engineers had a look at this issue and there is potentially more detail here than you want to know.

The core issue is that the information available from the JDBC driver is limited.  In the information returned from the JDBC driver the metadata tells us that the calculated field is numeric, but that is has a 0 scale.  A 0 scale can mean either that there should be no decimals, and that we should map to a long (which we do), or that the scale is unknown. The JDBC ResultSetMetadata API does not differentiate between these cases, and in the case of calculated fields, the scale is essentially going to be reported as "unknown".

See the following for more details (if you are interested) - https://stackoverflow.com/questions/11567099/resultsetmetadata-getscale-returns-0-when-using-aggregate-functions-like-min-o

It appears the best option is to add an explicit cast to the query, with SQL like the following:
select ......., cast(round(1 + one_mon_ror, 5) as NUMBER(6,5)) as ROR

Regards, Tim.

that fixed it, thanks!