GetxxxDCIValue (Avg/Max/Min) fails on floating point numbers

[Jmp_3f8h]

Hi,

on MSSQL (tried on 2012) the GetxxxDCIValue works only with integer values, not with floating points (the wiki states numeric type).

I tried to simulate "manually" the query using this statement (reported in this thread https://www.netxms.org/forum/configuration/getavgdcivalue-not-working/msg11517/#msg11517

SELECT avg(coalesce(idata_value,0)) FROM idata_<node_id> WHERE item_id=<dci_id> and idata_timestamp between <time_from> and <time_to>

but with floating point values I get the following error message:

Conversion failed when converting the varchar value '14.2999710' to data type int

Is this a restriction that applies only on MSSQL?

Best regards,
Marco

[andrey--k]

to take math functions you should convert data from varchar to digits.
I use transit table in separate shema for this purpose.

[Jmp_3f8h]

@andrey-k said in GetxxxDCIValue (Avg/Max/Min) fails on floating point numbers:

to take math functions you should convert data from varchar to digits.
I use transit table in separate shema for this purpose.

Hi andrey--k,

Maybe I didn't understand your reply but my concern was related to the original GetxxxDCIValue, I'm not interested in running the query "manually". I ran the query as per suggestion in the reported thread just as a "proof" for the result. So, I was interested in knowing if the GetAvgDCIValue (for example) fails on floating point numbers only using MSSQL as backend DB or if this is a behaviour common even to the other DB engines supported by NetXMS.

Best regards,
Marco

[victor]

Hi!

Seems to be implicit data conversion issue. Can you try modify this query somehow to make it work (maybe cast idata_value to numeric type)? If you will make it wor, I'll fix server code to handle it correctly

Best regards,
Victor

[andrey--k]

Can you try next example:

SELECT avg(coalesce(to_number(idata_value),0)) FROM idata_<node_id> WHERE item_id=<dci_id> and idata_timestamp between <time_from> and <time_to>

[Jmp_3f8h]

@andrey-k said in GetxxxDCIValue (Avg/Max/Min) fails on floating point numbers:

Can you try next example:

SELECT avg(coalesce(to_number(idata_value),0)) FROM idata_<node_id> WHERE item_id=<dci_id> and idata_timestamp between <time_from> and <time_to>

Hi!,

to_number isn't present in MSSQL, it can be substituted with CAST (as per Victor's suggestion), to have it working on int and float it could be casted always to float as in this example:

SELECT avg(coalesce(cast(idata_value as float),0)) FROM idata_<node_id> WHERE item_id=<dci_id> and idata_timestamp between <time_from> and <time_to>

I would like to suggest also a check for numeric in the where condition (and isnumeric(idata_value)=1), running the above query on my DB I've discovered some dirty unvalid values in my records that caused failing of the query.

The final query should be something like:

SELECT avg(coalesce(cast(idata_value as float),0)) FROM idata_<node_id> WHERE item_id=<dci_id> and isnumeric(idata_value)=1 and idata_timestamp between <time_from> and <time_to>

Is there a little hope to have a GetSumDCIValue implemented in the meanwhile you add this fix? 

Best regards,
Marco

[victor]

I've added fix for MS SQL as suggested.

Best regards,
Victor

[andrey--k]

Same for Oracle DB:

select avg(TO_NUMBER(idata_value,'99999.999999')) from IDATA_100 where ITEM_ID = '7'

Возможно надо будет увеличить маску (добавить пару-тройку девяток перед точкой).