5 Replies Latest reply on Sep 4, 2012 3:38 PM by Asle Frantzen

    AF: Return multiple Table Lookup values and concatenate them as a string?

    Asle Frantzen

      Hi guys

       

      I'm filling data into AF by doing a lookup into an Oracle database (through AFTables).

       

      All my lookups so far have been with a unique identifier, but now I'm faced with the challenge of returning a concatenated string of all results returned by a query. The case is a faults table where all the faults of the different equipment are stored. Next to the amount of faults they also want to see the years of each fault, so if the number of faults is 3, they want to see the following in the report I'm making:

      • 3 (1992, 1997, 2003)
      • 1 (2002)
      • 2 (1992, 1993)

      If I do a simple lookup having the attribute data type as 'string' I will only get the first result. If I change the data type to 'string array' I will get all the results, but then I can't utilize the results further on through OLEDB Enterprise. If only there was an array.tostring() function somewhere.

       

      Any tips or tricks on how to achieve this?

       

      Thanks!

        • Re: AF: Return multiple Table Lookup values and concatenate them as a string?
          Rick Davin

          I tend to favor custom data references.  I am walking proof that if all you have for a solution is a hammer then sooner-or-later every problem begins to look like a nail.

            • Re: AF: Return multiple Table Lookup values and concatenate them as a string?
              mhamel

              @Asle: You could create an AF data reference that works with AFTable and AFTables objects with LINQ to allow more complex queries to take place in memory. I suppose from your explanation that the table (from ORACLE) contains an equipment id of some sort, a date where the fault occurred and the category of faults. If it is the case, you would have to perform a query to count faults for chosen equipment id and category and do a second one to obtain an ordered list of years where a category of faults happened for a chosen equipment id.

               

              You can manipulate the table itself with AFTable.Table property. This is a native System.Data.DateTable object.

               

              Query 1

               
              SELECT COUNT(*)
              FROM MyTable
              WHERE EQUIPMENTID = 'ID'
              AND CATEGORY = 'CATEGORY'
              

               Query 2

               
              SELECT [DATE]
              FROM MyTable
              WHERE EQUIPMENTID = 'ID'
              AND CATEGORY = 'CATEGORY'
              GROUP BY [DATE]
              ORDER BY [DATE]
              

              You generated a csv list of years that you append to the number of faults and you get what you want!

                • Re: AF: Return multiple Table Lookup values and concatenate them as a string?
                  Asle Frantzen

                  Hi guys

                   

                  Thanks for replying. I was going to try your suggestion, Mathieu, but then I made a small discovery:

                   

                  Since writing this yesterday I have upgraded from OLEDB Ent. 2010 R2 to R3, and apparently the new version handles string arrays better than the previous one. Where I yesterday only had "System.String[]" as value for all my attributes when querying the data through OLEDB, it now seems like the strings in the array are concatenated almost like I want it to be.

                   

                  The data now looks like this: [1998, 2002, 2005]

                   

                  I still can't make it work directly in AF or PI System Explorer, but my main requirements were to have these data available in SQL Server and Reporting Services. So with a little substring magic I can now output the years just like I want it. Excellent :)

                   

                  There seems to be one or more extra characters added after the square brackets, but with this SQL I'm able to display just the years:

                   
                  SUBSTRING([Observation years, minor faults],2,LEN([Observation years, minor faults])-3) AS ObservationTimes
                  

                   

                   

                  I'll keep your suggestion at hand when (not if) our client asks to have the data available as an attribute as well.

                   

                  I would definitely like to have this available directly within the Table Lookup functionality in AF though. There are other cool stuff you can do, but they all seem to be related to numerical values only. And the same thing with Formula DR's. Numerical only, and nothing for string operations. More string stuff is high on my wanted list

              • Re: AF: Return multiple Table Lookup values and concatenate them as a string?

                Asle Frantzen @ Amitec

                If I change the data type to 'string array' I will get all the results, but then I can't utilize the results further on through OLEDB Enterprise.
                If you only care to use the results with PI OLEDB Enterprise and you are using these results in a .NET environment, then there might be no need to create a custom Data Reference.

                 

                In a nutshell, PI OLEDB Enterprise can handle a string array... just PI SQL Commander doesn't render it (other than say its type: System.String[]). In a .NET environment where you are using ADO.NET to query PI OLEDB Enterprise, you can read the value into a String array.

                 

                Hope this helps!