1 Reply Latest reply on Oct 31, 2018 12:46 PM by Tomas Stark

    SELECT statement problem with PI Interface for RDBMS

    Tomas Stark

      HI,

       

      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,

      Tomas

        • Re: SELECT statement problem with PI Interface for RDBMS
          Tomas Stark

          I solved the problem by using REPLACE in the SELECT statement. Due to localization (on both the RDBMS and the PI Interface servers?) I had to do a replace from ',' to '.' in order for it to work.

           

          But, I got double tag values for each value even though Location2 is set to 0. This was fixed by adding "TOP 1" in the SELECT statement.

           

          So, this is our final and working SELECT statement:

           

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

           

          Regards,

          Tomas

          1 of 1 people found this helpful