This project has moved. For the latest updates, please go here.

How to correlate output device/phasor with input device/phasor when building SQL query?

Jun 13, 2014 at 9:05 PM
Hi, Guys,

I am building a SQL query to get information out of my openPDC SQL database, containing both input and output device, measurement and phasor info. The reason is that I have different names and ID codes for the same device/phasor in input and output streams, and this will serve as a mapping table for me.

I am stuck when I tried to correlate the input and output device/phasor, because I have different ID Codes/Names. Could you please tell me the key to correlate them? Below is what I have so far... (You can see that I still need to correlate with OutputStreamDeviceDetail and OutputStreamDevicePhasor.)

USE openPDC
SELECT MeasurementDetail.CompanyAcronym,
MeasurementDetail.CompanyName,
DeviceDetail.AccessID,
MeasurementDetail.DeviceAcronym,
MeasurementDetail.PhasorSourceIndex,
MeasurementDetail.SignalReference,
MeasurementDetail.Adder,
MeasurementDetail.Multiplier,
MeasurementDetail.PhasorLabel,
MeasurementDetail.PhasorType,
OutputStreamDeviceDetail.IDCode,
OutputStreamDeviceDetail.Acronym,
OutputStreamDevicePhasor.Label,
MeasurementDetail.AlternateTag
FROM MeasurementDetail
INNER JOIN DeviceDetail
ON DeviceDetail.ID = MeasurementDetail.DeviceID
INNER JOIN OutputStreamDeviceDetail
ON OutputStreamDeviceDetail.AdapterID = 1
ORDER BY MeasurementDetail.CompanyAcronym, MeasurementDetail.DeviceAcronym, MeasurementDetail.PhasorSourceIndex

Thanks!
Frankie
Jun 13, 2014 at 9:12 PM
Hi Frankie!

This is a question I submitted to Barbara last year, and got no good answer. When we change the device names, phasor names, various IDs in the output stream, you cannot (I believe) find your way back to the inputs. There are missing columns, I believe, in the output tables that would be set on insert and not updated ever, containing the source ID. This way the names and table-local IDs no longer are needed in the joins. This was never requested of Richie, et al


Coordinator
Jun 16, 2014 at 5:52 PM
Edited Jun 16, 2014 at 6:16 PM
Doesn't something like this work:
SELECT
      OS.Acronym AS OutputStream,
      IM.DeviceAcronym AS InputDeviceAcronym,
      IM.DeviceName AS InputDeviceName,
      IM.SignalReference AS InputSignalReference,
      IM.PhasorLabel AS InputPhasorLabel,
      IM.PhasorType AS InputPhasorType,
      IM.Phase AS InputPhase,
      OD.Acronym AS OutputDeviceAcronym,
      OD.Name AS OutputDeviceName,
      OM.SignalReference AS OutputSignalReference,
      OP.Label AS OutputPhasorLabel,
      OP.Type AS OutputPhasorType,
      OP.Phase AS OutputPhase
  FROM OutputStreamMeasurementDetail AS OM INNER JOIN MeasurementDetail AS IM ON 
  OM.PointID = IM.PointID INNER JOIN OutputStreamDeviceDetail AS OD ON 
  OM.AdapterID = OD.AdapterID AND CHARINDEX(OD.Acronym + '-', OM.SignalReference) = 1 INNER JOIN OutputStreamDetail AS OS ON
  OS.ID = OM.AdapterID LEFT OUTER JOIN (SELECT ROW_NUMBER() OVER (PARTITION BY OutputStreamDeviceID ORDER BY LoadOrder) AS SourceIndex, * FROM OutputStreamDevicePhasor) AS OP ON
  OP.OutputStreamDeviceID = OD.ID AND CAST(RIGHT(OM.SignalReference, LEN(OM.SignalReference) - CHARINDEX('-', OM.SignalReference) - 2) AS INT) = OP.SourceIndex AND
  SUBSTRING(OM.SignalReference, CHARINDEX('-', OM.SignalReference) + 1, 1) = 'P'
Marked as answer by ritchiecarroll on 7/3/2014 at 1:12 PM
Jul 2, 2014 at 4:34 PM
Edited Jul 2, 2014 at 4:35 PM
Nice!! I had to admit that it took me some time to understand the code, but once I did, it worked perfectly with some adjustments to my needs.

Thanks a lot Ritchie!

Maybe we should also think about including more keys in the tables to better correlate them. These are the ones that lacks dynamic correlation:

OutputStreamDevice - OutputStreamMeasurement: currently the link is through the Acronym in SignalReference, preferably adding a OutputStreamDeviceID in the OutputStreamMeasurement table, like the one in OutputStreamDevicePhasor.

OutputStreamDevicePhasor - OutputStreamMeasurement: currently the link is the LoadOrder in OP and SourceIndex in OM. This is a static list and had caused problems before when deleting signals. Would PointID be useful here? So far as I know, PointID is unique in both input and output measurements. Use either magnitude's or angle's PointID to label the corresponding Phasor. BTW, this is also a problem in the input phasor and input measurement.

Just a thought...

Thanks!
Frankie
Coordinator
Jul 3, 2014 at 9:12 PM
Yeah - we never really designed the schema to navigate backwards so you could link all that information together. Like you say, just a few extra keys would have made that much more simple. Anyway, glad it worked for you!

Thanks,
Ritchie