8 Replies Latest reply on Mar 5, 2018 1:03 PM by esancha

    Filtered PIOLEDB Average

    esancha

      Hi!

       

      I'm trying to get an average filtered through OLEDB, but it does not work as I expect

       

      This query runs OK

      SELECT value

      FROM piarchive..piavg

      WHERE tag = 'FFH:A_024'

      AND time BETWEEN '01-mar-2018 06:00' AND '02-mar-2018 06:00'

      AND calcbasis = 'TimeWeighted'

      AND 'FFC:W_004' >= 50

       

      But this other don't returns nothing

      SELECT value

      FROM piarchive..piavg

      WHERE tag = 'FFH:A_024'

      AND time BETWEEN '01-mar-2018 06:00' AND '02-mar-2018 06:00'

      AND calcbasis = 'TimeWeighted'

      AND 'FFC:W_004' >= 50

      AND ('FFH:A_024' BETWEEN 55 AND 75)

       

      I tested with and without parenthesis and if I remove one of the two conditions and I put for example AND 'FFH: A_024' > = 55 also works right

       

      There is enough data that meets the condition

      timevalue
      01/03/2018 6:38:0665,86
      01/03/2018 8:10:0665,46
      01/03/2018 11:09:1562,69
      01/03/2018 22:32:1561,95
      01/03/2018 22:59:2266,94
      01/03/2018 23:04:1561,54
      02/03/2018 0:10:0466,39
      02/03/2018 1:32:0465,61
      02/03/2018 2:15:3565,11
      02/03/2018 3:53:0463,9
      02/03/2018 5:13:3063,13
      02/03/2018 5:43:3063,41

       

      What am I doing wrong?

       

      Thanks in advance

        • Re: Filtered PIOLEDB Average
          wpribula

          Hello,

          You are missing one parameter filtersampletype. See my example query:

           

          SELECT *

               FROM piarchive..piavg

               WHERE tag = 'CDT158'

                   AND time BETWEEN 't-1d' AND 't'

                   AND timestep = '1h'

                   AND calcbasis = 'EventWeighted'

                   AND filterexpr = '''SINUSOID'' < 50'

                   AND filtersampletype = 'interval'

          2 of 2 people found this helpful
            • Re: Filtered PIOLEDB Average
              esancha

              Thanks a lot Wojciech

               

              I have changed it

               

              SELECT value

               

              FROM piarchive..piavg

               

              WHERE tag = 'FFH:A_024'

               

              AND time BETWEEN '01-mar-2018 06:00' AND '02-mar-2018 06:00'

               

              AND calcbasis = 'TimeWeighted'

               

              AND filterexpr ="'FFC:W_004' >= 50 AND 'FFH:A_024' BETWEEN 55 AND 75"

               

              AND filtersampletype = 'interval'

               

              and now the result is Column name ''FFC:W_004' >= 50 AND 'FFH:A_024' BETWEEN 55 AND 75' is invalid.

               

              With the previous syntax, it worked well, except that it did not do well between.

               

              My weekend begins in a moment, so I will read you on Monday.

               

               

              Thanks everyone for your help.

               

               

               

               

            • Re: Filtered PIOLEDB Average
              vkaufmann

              It might be likely that you are running into this bug. You can append the OPTION (SYNC CALLS) to the end of your original query and see if that works.

               

              --Vince

              1 of 1 people found this helpful
                • Re: Filtered PIOLEDB Average
                  esancha

                  Thanks Vince,

                   

                  I don't know how to use that option. Anyway, my intention is to execute that SQL statement through VBA, so if it is a SQL Commander configuration it would not work.

                  If indeed, as it seems, the problem is that bug, then I will have to find another way to do the calculation.

                   

                  Thanks you all.

                    • Re: Filtered PIOLEDB Average
                      gregor

                      Hi Emilio,

                       

                      Using OPTION (SYNC CALLS) does not require any specific client. You just append the string to the end of your query.

                      PI OLEDB (Classic) table piavg exposes columns filterexpr, filtersampletype and filtersampleinterval to set filter conditions. Have you tried the query example shared by Wojciech?

                       

                      What client are you enhancing with VBA (MS Excel, MS Word, PI ProcessBook)? 

                      1 of 1 people found this helpful
                        • Re: Filtered PIOLEDB Average
                          esancha

                          Thanks again.

                          Yes, I had tried it, but it did not work for me, because I had used double quotes instead of two simple ones, now I have copied and pasted it and it works, then I edited it to adapt it to my needs and I have already achieved what I was looking for, on the other hand, I see that BETWEEN works well in the criterion of the time range, but not in the expression of the filter.

                          SELECT *

                           

                          FROM piarchive..piavg

                           

                          WHERE tag = 'FFH:A_024'

                           

                          AND time BETWEEN '01-mar-2018 06:00' AND '02-mar-2018 06:00'

                           

                          AND calcbasis = 'EventWeighted'

                           

                          AND filterexpr = '''FFC:W_004'' > 50 AND 55<''FFH:A_024''<75'

                           

                          AND filtersampletype = 'interval'

                           

                          OPTION (SYNC CALLS)

                           

                          I'm using it on Access.

                          Thanks a lot all.