3 Replies Latest reply on Dec 13, 2017 6:49 AM by GreenTech

    PISDK: GetPointsSQL()

    Doddabasappa

      Hi,

       

      I would like to know syntax to use "tag in() and tag not in ()" together in GetpointsSQL() query.

       

      Works fine if I use only tag in (). But when I combine with tag not in(), its throwing error.

       

      Call to retrieve points failed.  [-260] SQL: invalid WHERE clause

       

                  PISDK.PISDK sdkroot = new PISDK.PISDK();

                  Servers piServers = sdkroot.Servers;

                  Server piServer = piServers["PI-Server"];

       

                  PointList filteredPIPointList = new PointList();

                  PointList finalPIPointList = new PointList();

                  string strQuery = "tag in ('sinusoid*') and tag not in ('sinusoidu*') ";

                 filteredPIPointList = piServer.GetPointsSQL(strQuery);

                 Console.WriteLine(filteredPIPointList.Count);

        • Re: PISDK: GetPointsSQL()
          jyi

          Hello,

           

          NOT IN () operator probabaly won't work in the pisdk query. The detailed syntax information can be found from pisdk's Programmers Reference help file or in below LiveLibrary documents.

          GetPoints Syntax

          And we have a rule in AND operator that the same attribute('tag') cannot be used twice. I imagine that this goes the same for GetPointsSQL as well. The difference between GetPoints vs. GetPointsSQL is detailed in below:

           

          GetPoints vs. GetPointsSQL

          • Re: PISDK: GetPointsSQL()
            Eugene Lee

            For your query, you can use this workaround instead.

             

            PointList filteredPIPointList = piServer.GetPointsSQL("tag in ('sinusoid*')");
            PointList filteredPIPointList2 = piServer.GetPointsSQL("tag <> ('sinusoidu*')");
            HashSet<PIPoint> checking = new HashSet<PIPoint>();
            foreach (PIPoint item in filteredPIPointList)
            {checking.Add(item);}
            foreach (PIPoint item in filteredPIPointList2)
            {
            if (checking.Contains(item))
            Console.WriteLine(item.Name);
            }
            
            1 of 1 people found this helpful
            • Re: PISDK: GetPointsSQL()
              GreenTech

              Tag ='sin*' and Tag <> '*u' works for me in GetPointsSQL()

               

              We had a similar query running for a customer within our integration product but we found GetPointsSQL() way too slow (>30 mins!) {the tag database was 250k points}, and you can only reference tag once in GetPoints()

              I'm not sure about Eugene's solution - you might want to try that on a system with 200k points!

               

              What we did is include a subsequent filter string (regex) that we tested (.Match()) as we scanned through the results and decided whether they should be included. This worked perfectly and lightning speed compared to GetPointsSQL.

              You could also use Lynq on your resultset as well (if you like Lynq - but it's not faster).