9 Replies Latest reply on Oct 17, 2018 2:49 PM by mehsham

    Using picomp2 table to query two tags to return in two columns

    mehsham

      Hi,

       

      Using the Pi Interpolation table I am able to create a table with individual colums with their corresponding tag values. I want to create similar table in another words table with two individual tag value colums but using 'picomp' table. I used Common Table Expression methodology to create the first table and below is the code. I have also included the code that I am using with picomp but it doesn't seem to work. I am using ignition so you may find the syntax a tad bit different. Your help is greatly appreciated.

       

      Table created using piinterp

      piinterp code snip

      __________________

      SELECT CAST(Line1.time as datetime), Line1.value as FIT12513, Line2.value as AIT12515 FROM piinterp2 Line1 INNER JOIN piinterp2 Line2 ON Line2.time=Line1.time

      where Line1.tag = 'XX10001_FIT12513_VAL' and Line2.tag = 'XX10001_AIT12515_VAL' and Line1.time BETWEEN '{Start Date}' and '{End Date}' and line1.timestep = '1h'

      ____________________________________________________

       

      picomp2 snip

      SELECT Line1.[datetime], Line1.[FIT12513], NULL as [AIT12515]  FROM ( SELECT cast(time as datetime) as [datetime], value as [FIT12513]

      FROM picomp2 where tag like 'XX10001_FIT12513_VAL' and time between '{Start Date}' and '{End Date}') Line1

      UNION

      SELECT Line2.[datetime], Line2.[AIT12515], NULL as [FIT12513]  FROM ( SELECT cast(time as datetime) as [datetime], value as [AIT12515]

      FROM picomp2 where tag like 'XX10001_AIT12515_VAL' and time between '{Start Date}' and '{End Date}') Line2

        • Re: Using picomp2 table to query two tags to return in two columns
          jyi

          Hi Faisal,

           

          This is not possible as PI OLEDB picomp table cannot create a column per tag.

          Even if it works, it will be very often the case where both columns have many holes due to compression and data collection frequency. The two timestamps will simply be different.

          Is there any reason why you wish to use picomp table instead of piinterp?

          2 of 2 people found this helpful
          • Re: Using picomp2 table to query two tags to return in two columns
            Lal_Babu_Shaik

            Hi Faisal

             

            @Jinmo Yi is right.  You can test the same using Datalink Timed vs Compressed Data.  Compressed/Snapshot timestamps may differ unless you are writing same timestamp and values to different tags. If both time stamps matches then compressed and interpolated values can be used the query else you will not be able to combine two columns(tabs) with different timestamps (Primary).

             

            Thanks

            Lal

              • Re: Using picomp2 table to query two tags to return in two columns
                mehsham

                I see what you're saying Lal. eg. if comp table was to work somehow this is what the result would be

                Datetime                     TAG1       TAG2

                Sept 21 10:30              2.22           0

                Sept 21 10:45                0             1.21

                 

                I shall try to see if I can use piinterp table somehow.

                  • Re: Using picomp2 table to query two tags to return in two columns
                    jyi

                    Like Lal is saying, PI DataLink might be a good go-to tool for a quick checks. PI DataLink offer, in large, 3 data query types( Compressed-raw-, Sampled-Interpolated-, and Timed Data)

                    The compressed data is the same one as comp or comp2 table:

                    Datetime                    TAG1      TAG2

                    Sept 21 10:30              2.22          0

                    Sept 21 10:45                0            1.21

                     

                    So each tags bring its own timestamp.

                     

                    If you can set a base timestamps from tag1, then you could use a timed data. Timed data is where you supply a set of timestamps for interpolation as opposed to the given start, endtime and time interval.

                    Please try this if you are interested. The counterpart to Timed Data can be written in OLEDB as below:

                    SELECT a1.time, a1.value as "TAG1", a2.value as "TAG2"

                    FROM piarchive..picomp2 a1

                    INNER JOIN piarchive..piinterp2 a2 ON a1.time = a2.time

                    WHERE a1.tag = 'TAG1' AND a1.time BETWEEN 'y' AND 't'

                    AND a2.tag = 'TAG2'

                     

                    Using the TAG1's timestamps, you can interpolate the values from TAG2. I hope this helps.

                    1 of 1 people found this helpful
                      • Re: Using picomp2 table to query two tags to return in two columns
                        mehsham

                        Thank you Jinmo! I ended up using interp2 syntax initially but I will test out your code when I get a chance. Thanks again for all your help and support.

                        • Re: Using picomp2 table to query two tags to return in two columns
                          mehsham

                          Hi Jinmo, I tried your methodology and it more or less is yielding the same result. I really appreciate your input though. So, I would like to pick your head again. Please see below table 1, and table 2 and it's sql query script. I want to union the two tables (Table 1 and 2) however the initial script that i had mentioned works but not the way I want it to work.

                          Table 1

                          Table 1: SQL Script

                          SELECT Line1.[datetime], Line1.[T34501], NULL as [T34601]  FROM ( SELECT time as [datetime], value as [T34501]

                          FROM picomp2 where tag = 'P003310_T34501_Isolated_Stacks' and time between '2018-09-20' and '2018-09-30') Line1

                           

                           

                          Table 2

                          Table 2: SQL Script

                          SELECT Line2.[datetime], NULL as [T34501], Line2.[T34601] FROM ( SELECT time as [datetime], value as [T34601]

                          FROM picomp2 where tag = 'P003310_T34601_Isolated_Stacks' and time between '2018-09-20' and '2018-09-30') Line2

                           

                          When I try to union the two tables using the following query as seen below I get this error (please see below this script)

                          SELECT Line1.[datetime], Line1.[T34501], NULL as [T34601]  FROM ( SELECT time as [datetime], value as [T34501], value as [T34601]

                          FROM picomp2 where tag = 'P003310_T34501_Isolated_Stacks' and time between '2018-09-20' and '2018-09-30') Line1

                          UNION

                          SELECT Line2.[datetime], NULL as [T34501], Line2.[T34601]  FROM ( SELECT time as [datetime], value as [T34501], value as [T34601]

                          FROM picomp2 where tag = 'P003310_T34601_Isolated_Stacks' and time between '2018-09-20' and '2018-09-30') Line2

                           

                          ERROR

                           

                          In the union sql query when i change the other of NULL as NULL as [T34501], Line2.[T34601] to Line2.[T34601], NULL as [T34501] then it returns a table but now the one I want as all the values converge into one column (Please see below)

                          SELECT Line1.[datetime], Line1.[T34501], NULL as [T34601]  FROM ( SELECT time as [datetime], value as [T34501]

                          FROM picomp2 where tag = 'P003310_T34501_Isolated_Stacks' and time between '2018-09-20' and '2018-09-30') Line1

                          UNION

                          SELECT Line2.[datetime], Line2.[T34601], NULL as [T34501]  FROM ( SELECT time as [datetime], value as [T34601]

                          FROM picomp2 where tag = 'P003310_T34601_Isolated_Stacks' and time between '2018-09-20' and '2018-09-30') Line2

                           

                          Problem is I want T34601 tag values to show on the right side.

                           

                          BOTTOM LINE

                          Could you please help me with the sql query that will combine Table 1 and Table 2. Thanks!