2 Replies Latest reply on Jan 10, 2017 1:56 PM by igreguri

    SQL query for PI Relational Datasets

    igreguri

      I have some SQL query for storage tanks status, and I want to improve with one more parameter for comparison...

      query.jpg

       

      status.jpg

       

      How to insert one more parameter with result in column TimeToFill (after FlowTov column)?

      WHERE s1.tag LIKE 'RT%TIMETOFILL.PV'

       

      Regards,

      Igor

        • Re: SQL query for PI Relational Datasets
          gregor

          Hello Igor,

           

          The following should work for you as an interims view even I am unsure if this is the most elegant way to do it. Please feel free to remove those columns you don't need e.g. the ones showing timestamps.

           

          SELECT t1.tag tag1, t1.time time1, t1.value value1, t2.tag tag2, t2.time time2, t2.value value2, t3.tag tag3, t3.time time3, t3.value valu3
          FROM piarchive..pisnapshot t1
          INNER JOIN piarchive..pisnapshot t2 ON SUBSTR(t1.tag, 3, 4) = SUBSTR(t2.tag, 3, 4)
          INNER JOIN piarchive..pisnapshot t3 ON SUBSTR(t1.tag, 3, 4) = SUBSTR(t3.tag, 3, 4)
          WHERE t1.tag LIKE 'RT%FLOWTOV.PV'
          AND t2.tag LIKE 'RT%.STATUS.OWNER.MDE'
          AND t3.tag LIKE 'RT%TIMETOFILL.PV'
          OPTION (FORCE ORDER)