AnsweredAssumed Answered

SELECT statement problem with PI Interface for RDBMS

Question asked by Tomas Stark Champion on Oct 30, 2018
Latest reply on Oct 31, 2018 by Tomas Stark



we have a database with manually entered data from roundings.


The value column is of type NVARCHAR, which is not supported by the RDBMS interface, so we do a CAST to VARCHAR in the SQL statement.


The target PI Point is of type FLOAT32.


Since the values may sometimes be non-numeric (as in the example below), we check for ISNUMERIC = 1.


We only want to read the last written value to the RDBMS, so we have set LOCATION2 to 0 and specify ORDER BY res_date DESC in the SQL statement.


Everything seems to work fine, except that the value received for 18-10-30 09:20 will be truncated to 0. So it seems the interface can not identify the decimal point and convert from VARCHAR to FLOAT32 for some reason. We have tried to do a CAST to DECIMAL, FLOAT etc, but this will not be accepted by the ODBC 13 driver.


Values in the RDBMS:

181030 Skärmklipp - värden.jpg

We have also tried to set LOCATION2 to 1, and then all rows will be fetched as expected. But the values will all be truncated.


181030 Skärmklipp - location.jpg


SELECT res_date, CAST(resultat AS VARCHAR), 0 FROM [cwd_prod_ny].[dbo].[viewResultat] WHERE todo_jobbid = ? AND ISNUMERIC(resultat) = 1 ORDER BY res_date DESC;


Best regards,