Hi Tiago, The content is not messed up but converted to scientific notation. This is valid in json and various other data formats. Apparently either the database or NiFi considers decimal(x,7) the point at which scientific notation is more suitable.
Since you’re going to CSV format, you can convert the values to string with the desired format right in the SQL query. Regards, Isha Van: Tiago Luís Sebastião (DSI) <[email protected]> Verzonden: woensdag 19 april 2023 12:56 Aan: [email protected] Onderwerp: NiFi 1.19.1 - ExecuteSQL - Decimal Precision above 6 returns "OE-7" Hi, I’m using a standalone deployment (Nifi 1.19.1) that runs directly on the server. I’ve noticed something tricky for the first time… When executing a query with Nifi processor ExecuteSQL the maximum decimal precision that it returns is 6, anything above that returns “0E-7”. Query: “ select 0.0000000::decimal(38,7) as Test1 -- NOT OK -> Returns 0E-7 ,0.0000000::decimal(38,6) as Test2 – OK -> Returns 0,000000 ,0.0000000::decimal(12,7) as Test3 -- Needed -> NOT OK -> Returns 0E-7 ,0.0000000::decimal(12,6) as Test4 -- OK -> Returns 0,000000 “ ExecuteSQL . List Queue . View content: " Obj___avro.schemaö_{"type":"record","name":"NiFi_ExecuteSQL_Record","namespace":"any.data","fields":[{"name":"TEST1","type":["null","string"]},{"name":"TEST2","type":["null","string"]},{"name":"TEST3","type":["null","string"]},{"name":"TEST4","type":["null","string"]}]}_avro.codec_nulléõ žSø®²›_yá‚_„_@__0E-7__0.000000__0E-7__0.000000éõ žSø®²›_yá‚_„ " After ExecuteSQL I use a ConvertRecord with AvroReader and CSVRecordSetWriter but since the content is already messed up it converts to: “0E-7; 0.000000;0E-7;0.000000” Any idea if this is a known issue or how to solve it? ----- ExecuteSQL Specs Database Connection Pooling Service: DBCPConnectionPool-Netezza -> IBM Cloud Pak for Data System (PostgreSQL) setSQL: select 0.0000000::decimal(38,7) as Test1, 0.0000000::decimal(38,6) as Test2, 0.0000000::decimal(12,7) as Test3, 0.0000000::decimal(12,6) as Test4 Normalize Table/Column Names: false Use Avro Logical Types: false Compression Format: NONE Default Decimal Precision: 10 Default Decimal Scale: 0 ----- Thanks in advance. Tiago Sebastião
