4 Replies Latest reply on Jun 26, 2009 5:03 PM by cescamilla

    Writing RDBMS data to PI when all the fields are in one column

    lchaylian

      Hello,

       

      Notmally when you read SQL data using RDBMS interface to PI you get one value by one column, it means one column by tag, for instance:

      Select A,B,C where fecha=#12-05-09#
      Where TagA = A, TagB=B and TagC=C

      Our need is to read in this way:

      Select values where fecha=#12-05-09#
      Where values = "A,B,C"

      So we have all the values in one field like comma separate string

       

      How can we read these ?

       

      Kind Regards

        • Re: Writing RDBMS data to PI when all the fields are in one column
          andreas

          Hi,

           

          vCampus is focusing on the PI Developer community. Questions regarding PI Interfaces not related to vCampus should be send to the TechSupport (techsupport@osisoft.com).

           

          Said that, you might start experimenting with the following query:

          SELECT
          SUBSTRING(Table_1.value,
                    1,
                    CHARINDEX(',',Table_1.value,1)-1) AS a,
          SUBSTRING(Table_1.value,
                    CHARINDEX(',',Table_1.value,1) + 1,
                    CHARINDEX(',',Table_1.value,CHARINDEX(',',Table_1.value,1)+1) - CHARINDEX(',',Table_1.value,1)-1) AS b,
          SUBSTRING(Table_1.value,
                    CHARINDEX(',',Table_1.value,CHARINDEX(',',Table_1.value,1)+1) + 1,
                    LEN(Table_1.value) - CHARINDEX(',',Table_1.value,CHARINDEX(',',Table_1.value,1)+1)) AS c
          FROM Table_1

          Regards,

          • Re: Writing RDBMS data to PI when all the fields are in one column
            quangsun

            lchaylian --

             

            I'm not sure if I understand your question properly but if I think you trying to do is "UNPIVOT".   New versions of SQL Server have an "UNPIVOT" command where you can tranpose columns into rows.

             

            For example, given a table such as:

             

            LogTimeStamp  ColumnA   ColumnB  ColumnB
            6/19/2009        10            20            30
            6/20/2009        50            60            70

             

            You can unpivot by doing this command:

            SELECT [LogTimeStamp], [Column_Name], [Values]
            FROM myTable
            UNPIVOT ([Values] FOR [Column_Name] IN (ColumnA, ColumnB, ColumnC)) as unpvtTable

            The results will be:
            LogTimeStamp  Column_Name   Values
            6/19/2009        ColumnA           10
            6/19/2009        ColumnB           20
            6/19/2009        ColumnC           30
            6/20/2009        ColumnA           50
            6/20/2009        ColumnB           60
            6/20/2009        ColumnC           70

             

            Hope this helps.

             

            Also Andreas, this isn't necessarily an interface issue.  In any case if vCampus is a true community, then I think any question is fair game that any community can assist if possible.

            • Re: Writing RDBMS data to PI when all the fields are in one column
              cescamilla

              There is a parameter in SQL that allows you to pass values the way you want...

               

              It's called IN... as IN (as magically as 'between', I know.)

              Select sinusoid, cdt158, sinusoidu where values in (15, 23, 34, 56, 34) and timestamp between sysdate-1 and sysdate

              So, using IN will do what you want for ALL tags, not just one, but you can always force it with AND and OR like "WHERE (taga = 'sinusoid' AND value in (12, 23, 34, 45)) OR (tagb = 'cdt158' AND value in (9, 98, 87, 76, 65))" and the 'between' keyword is used to compress this "timestamp > sysdate-1 and timestamp < sysdate" into this "sysdate between sysdate-i AND sysdate" really cool, eh?

               

              As someone commented, you can use 'unpivot' or even some 'union's...

               

              Try something like

              select taga where fecha=#12-05-09# where values in "A, B, C"
              union
              select tagb where fecha=#12-05-09# wwhere values in "D, E, F"
              union
              select tagc where fecha=#12-05-09# where values in "G, H, I"

              Best of lucks!