We have PI as part of our FactoryTalk Historian, so it's Pi but without the online access benefits. I can get here but that's about it. Everything else is blocked.
Programs: PIEFGEN, PIOLEDBENT, PI System Explorer, PI SQL Command Lite
All software is set up, configured, running.
I am creating parent and child event frames.
Attribute data is being stored.
I will simplify naming conventions, to protect I.P.
The Parent Event Frame uses template Procedure_Parent
Attributes: Name, Age, Hobby
Pulse trigger with several attributes
The Child Event Frames uses tmeplate PhaseStep_Child
Attributes: Name, Age, FavoriteColor
Step Trigger with Attributes
I can view the Event frame and child events in Pi system Explorer. I can query various Parent event frames via SQL Commander.
I would like to do the following but I'm having trouble figuring out all the Joins, fields, to transpose or not to transpose, etc.
Given a Parent Event Frame by Name (unique identifier), show the associated Child event frames (Start Time, End Time), the attribute name and values for child event frame. The Data will go into a SSRS report so Attribute names and value pairs are needed.
Given the example query below I know I'll be querying at least these tables:
but I'm not sure how to format the query and whether or not I need to transpose the data?
Desired Output data (formatted for this post)
Becky (Age/12, FavoriteColor/ Blue)
Susie (Age/6, Favorite Color/Yellow)
Child n+1 (Attribute Name/Value,.....)
This example query will give me the attributes of the Parent but doesn't return the attribute name or the children
SELECT ef.Name, ef.StartTime, ef.EndTime, s.*
SELECT TOP 100 ID, Name, StartTime, EndTime
WHERE Name =N'Dad'
ORDER BY StartTime DESC
INNER JOIN [AFDB].[EventFrame].[EventFrameAttribute] efa ON efa.EventFrameID = ef.ID
INNER JOIN [AFDB].[Data].[EventFrameSnapshot] s ON s.EventFrameAttributeID = efa.ID
OPTION (FORCE ORDER, EMBED ERRORS)
This gets me roughly (there's a whole lot more data because of s.* which I fix later)
Dad, 52, Football
Mom, 50, Sewing
No Attribute Name/ Value pairing
How do I do this?