11 Replies Latest reply on Nov 8, 2016 8:50 AM by JPBarnard

    AF Table Lookup not working for timeseries

    CameronLee

      Hi,

       

      I have an attribute template that uses a table lookup that works fine when not using the history option but fails when turned on eg:

       

      • Table defined:ValidatedInputs with columns starttime (datetime), tagname (string), Value (Double);
      • data is consistent and the records are distinct with respect to the tagname and time;
      • Attribute Template: Service Hours: SELECT Value FROM ValidatedInputs WHERE Tagname = @[Process Node ID]+'_MAN.dvServiceHours';UOM=h;RWM=DBNull;TC=StartTime;
      • I am using the attribute "Process Node ID" combined with the string '_MAN.dvServiceHours' to find the column;
      • It works fine when using the rule: select first row matching criteria but fails when using "Table Provided time series data" ie an attribute with history;
      • it also works using this syntax: SELECT Value FROM ValidatedInputs WHERE Tagname = 'GDP101_MAN.dvServiceHours';UOM=h;RWM=DBNull
      • it works if the whole column reference is in a single attribute reference too: eg if i make an attribute: Service Hours Tag, with the value: GDP101_MAN.dvServiceHours and have an attribute config in "Service Hours" as: SELECT Value FROM ValidatedInputs WHERE Tagname = @[Service Hours Tag];UOM=h;TC=StartTime;RWM=DBNull;

       

      The error is:

      The data method 'InterpolatedValue' is not supported on attribute 'Attribute 'Process Node ID' in Element 'DP101' in Element 'Darling Downs' in Element 'Sites''

       

      It looks like AF can't perform string concatenation in conjunction with the history mode turned on in the where clause .... weird ...

       

       

      Cheers,

       

      Cameron.

        • Re: AF Table Lookup not working for timeseries
          CameronLee

          PI System Explorer 2016 against AF Server 2015 R2

          • Re: AF Table Lookup not working for timeseries
            dmoler

            Hi Cameron,

             

            I'm not able to reproduce the problem - when I mimic the setup I can query for a time range matching the one in the table.

             

            Does the Process Node ID use a data reference?

              • Re: AF Table Lookup not working for timeseries
                CameronLee

                Hi David,

                 

                Thanks for replying:  ProcessNodeID is a StringConcat.  i've also tried StringBuilder.

                 

                I created a new Attribute Process Node ID1 which was just a string and that works (using the same syntax for the query).  I think that WHERE clause cannot reference attributes that use StringConcat (for the history option)...

                 

                smells like a bug....

                 

                This workaround will keep me going for now, but I can't deploy this database into production as our design relies on using StringConcat to build the data references.

                 

                Cheers,


                Cameron.

              • Re: AF Table Lookup not working for timeseries
                Roger Palmen

                Reading the documentation (https://livelibrary.osisoft.com/LiveLibrary/content/en/server-v7/GUID-C0476274-E77C-4FEE-91B7-17367BA4B133 ), the "+" sign in your WHERE clause is likely interpreted as a numeric addition, leading to the AFSDK trying to interpolate the string value of the attribute. And that won't work.

                 

                 

                So i'd go for the explanation that there is no bug and the best option is to use the stringbuilder to prepare the entire string for the WHERE clause, instead of trying to concatenate in the attribute configstring.

                  • Re: AF Table Lookup not working for timeseries
                    CameronLee

                    HI Roger,

                     

                    Apologies for sounding flippant, but i did try building the WHERE column reference by a few different methods, and I still got the error (wherever a string concat was involved).  Please note: When i used "select first row matching criteria" it works fine, and it is working fine now (now that the @[Process Node ID] no longer uses a StringContcat / StringBuilder) with the +'sometext' in the where clause.  Incidentally, i did read the help article you mention as well as the DataColumn.Expression Property reference mentioned in the help and i was a bit more confident in my proposition that i was using an appropriate syntax.

                     

                    Once again, thanks for chiming in to help.

                     

                    Cheers,

                     

                    Cameron.

                  • Re: AF Table Lookup not working for timeseries
                    JPBarnard

                    Hello

                    I can confirm a similar problem. I have opened a tech support case with OSIsoft. We have the following modular, layered design (shown here redacted and simplified):

                     

                    Element A

                         Attribute A := String Builder : %Element%

                        Child B (provides specialised services to parent)

                               Attribute B := String Builder : '..|Attribute A'

                              Attribute C := Table Lookup : SELECT SomeID FROM AFTableA WHERE SomeField=@[Attribute B]

                              Attribute D := Table Lookup:  SELECT SomeValue FROM AFTableB WHERE SomeField=@[Attribute C]; TC=TimeField;

                     

                    We use a periodic AF Analysis to historise the above Attribute D value to a PI Point. It only writes to the PI Point when the above Attribute D lookup sets TC. But, the above Attribute D table lookup fails when one sets TC and uses an attribute in WHERE that uses a String Builder DR or even a TABLE LOOKUP that takes an attribute with String Builder DR, like above (Attribute C).

                     

                    The above worked with AF Client 2016. After upgrade to 2016 R2, it fails. I have tested with both clients: AF 2016 and AF 2016 R2. The problem seems to be in the latest AFSDK. It is not on the server side (is my guess).

                     

                    I have required urgent support, as the above kills the above design pattern, which is the best for our particular use cases.