Instruments & Junction Box Connections

One of the really cool features of APS Reporter/SP is the Query Builder.

If you've worked with almost any database tools, you've seen graphical query builders. They help build SQL statements by dragging & dropping tables, creating joining, selecting fields, etc. Mostly they work much the same, with just some minor graphical changes, and usually they are great for simple queries, but have difficulty with complex queries. When it comes to writing complex queries, with sub-queries, nested unions, etc, it's back to directly coding SQL statements.

Novice users also generally have problems with Query Builders. Yes, they're graphical, and they are supposed to make it easy to create SQL statements, but they still use arcane Table & Field names, and the user still needs to understand how the database fits together. Despite being a "graphical" tool, they're still a barrier to new users getting up to speed.

For APSR, we have implemented a Query Builder that can handle almost any SQL statement, no matter how complex, all graphically. The weird field names now have clear descriptions of each field, and Virtual Tables & Fields simplify the database structure. Suddenly complex queries aren't so complex any more!

Here's a typical example. We wanted to create a simple Instrument Index Report for SPI, with the usual fields (Tag, Service, Manufacturer, Model, I/O Type, etc) plus we also wanted to include the Junction Box that the Instrument is connected to (ie where the field device is wired to). Simple enough requirement, but the Query is non-trivial.

The first step was to select all of the simple fields from the Component table in SPI:

Fortunately, the Virtual Fields in APSR make this really easy. Almost all of the fields we want are included as Virtual Fields in the Component table, so we just need to pick them. There's no need to join to the various supporting tables (like Function Types, I/O Types, Equipment, Manufacturer, Model, etc).

The only complicated field in this Report will be the Junction Box that the field devices connect to. To identify the correct Junction Box, the Query uses a sub-query with Wire Group (signal) information to find, for each signal, the lowest signal sequence number in a Junction Box. Then a second sub-query is used to find the particular Junction Box that contains that signal sequence number.

In APSR, sub-queries are defined as "Derived Tables". Creating a Derived Table produces a new work-space for us to build our sub-query.

The first sub-query actually finds the lowest wire GROUP_SEQ number for each WIRE_GROUP_ID, for wires that are connected to Junction Boxes (CATEGORY_SEQ = 6).

Then, we use the another Derived Table to use the minimum GROUP_SEQ from above to identify the correct Panel (Junction Box).

Our first Derived Table appears in the middle, with only the two selected fields showing. This second Derived Table has two outputs, the CMPNT_ID to identify the field device, and the Panel Name (which conveniently uses another Virtual Field to simplify our query). This Derived Table appears in our Main query, where we join to the Component table, and select the Panel name (using an Outer Join, as not all Instruments are connected to Junction Boxes).

Switching to the Query Results, we get:

The final SQL Query Statement is shown below, although fortunately we don't ever need to actually work with this. You could copy-and-paste it into your own APSR system, although the full report is included as a Sample Report in the installation package. (Note that this Query will not work in other tools as it uses APSR Virtual Fields that are not supported by other applications)

Select
                    COMPONENT.CMPNT_NAME,
                    COMPONENT.CMPNT_SERV,
                    COMPONENT.CMPNT_FUNC_TYPE_DESC,
                    COMPONENT.CMPNT_FUNC_TYPE_NAME,
                    COMPONENT.PLANT_NAME,
                    COMPONENT.AREA_NAME,
                    COMPONENT.UNIT_NAME,
                    COMPONENT.LOOP_NAME,
                    COMPONENT.CMPNT_MOD_NAME,
                    COMPONENT.CMPNT_MFR_NAME,
                    JUNCTION_BOX.PANEL_NAME As FIRST_JB,
                    COMPONENT.LOOP_DWG_NAME,
                    COMPONENT.PID_DWG_NAME
From
                    COMPONENT
                    Left Join
                    (Select WIRE_GROUP.CMPNT_ID,
                      WIRE_TERMINAL.PANEL_NAME
                    From WIRE_GROUP
                      Inner Join WIRE_TERMINAL On WIRE_GROUP.WIRE_GROUP_ID = WIRE_TERMINAL.WIRE_GROUP_ID
                      Inner Join (Select Min(WIRE_TERMINAL.GROUP_SEQ) As MIN_GROUP_SEQ,
                        WIRE_TERMINAL.WIRE_GROUP_ID
                      From WIRE_TERMINAL
                        Inner Join PANEL On WIRE_TERMINAL.PANEL_ID = PANEL.PANEL_ID
                      Group By WIRE_TERMINAL.WIRE_GROUP_ID,
                        PANEL.PANEL_CATEGORY_SEQ
                      Having PANEL.PANEL_CATEGORY_SEQ = 6) FIRST_JB_CONNECT 
                            On WIRE_GROUP.WIRE_GROUP_ID = FIRST_JB_CONNECT.WIRE_GROUP_ID 
                            And WIRE_TERMINAL.GROUP_SEQ = FIRST_JB_CONNECT.MIN_GROUP_SEQ
                    Where WIRE_TERMINAL.WIRE_GRP_LEVEL = 1) JUNCTION_BOX
                            On COMPONENT.CMPNT_ID = JUNCTION_BOX.CMPNT_ID
Where
                    COMPONENT.CMPNT_ID <> 0 And
                    COMPONENT.PLANT_ID <> 0
Order By
                    COMPONENT.LOOP_NAME,
                    COMPONENT.CMPNT_NAME

Sub-queries are an incredibly powerful tool for expanding your Reports, and with the APSR Query Builder creating Sub-queries (or Derived Tables) is as simple as almost any other query.

If you'd like to see some other sample queries and reports for SPI, check the relevant Wiki page. More information is also available from the various links to the right.

If you have any queries (pun?) or comments, feel free to add them below.

Regards,

Brian Raynor.