How to use local variable and LOOP in SQL query for PI OLEDB Enterprise?

Discussion created by jamliu on Sep 20, 2012
Latest reply on Feb 27, 2019 by gregor

Hi all,


   I am trying to use local variable and LOOP in the SQL query for accessing AF, but tried several things without luck.


   My goal is to create a "dataset" for PI Table webpart that receives a single string as the parameter from another webpart via webpart connection. The passed string contains multiple AF data attributes paths which are separated by ";". ex: "\\AFServer\AFDatabase\Element1|Attribute1;\\AFServer\AFDatabase\Element2|Attribute2;"


   So I'd like to pull these attributes' values with timestamp for the past specific period of time in the PI table, but I cannot find a way to utilize local variable and LOOP in the SQL query to parse the attribute string and loop through it, or don't know if this is even supported.


   Any idea? Thank you.


  p.s. Here is the sudo code to better explain my goal.



Attribute_path_parameter = "\\AFServer\AFDatabase\Element1|Attribute1;\\AFServer\AFDatabase\Element2|Attribute2;"

Declare @StartIndex as INT     
Declare @EndIndex as INT
Declare @recur as INT
Declare @AttrPath as CHAR(200)

Set @StartIndex = 0
Set @EndIndex = instr(SourceString,';',1, recur)
Set @recur = 1 

While (EndIndex <>0)
  Set @AttrPath =  substr(SourceString,@StartIndex,@EndIndex)

  SELECT eh.Name as Element, ea.Name as Attribute, ar.Value as Value, ar.Time as Timestamp
  FROM [Notification].[Asset].[ElementHierarchy] eh
  INNER JOIN [Notification].[Asset].[ElementAttribute] ea ON ea.ElementID = eh.ElementID
  INNER JOIN [Notification].[Data].[Archive] ar ON ar.ElementAttributeID = ea.ID
  WHERE eh.path = Attribute_path_parameter
  and ar.time between '*-2h' and '*' order by ar.time Desc

  Set @StartIndex = @ EndIndex + 1
  Set @recur++
  Set @EndIndex = instr(SourceString,';',1, @recur)