Sample SQL Statements Utilized - j5 - 28.0 - Help - Hexagon PPM

j5 Wonderware Historian Datasource Connector Help

Language
English (United States)
Product
j5
Search by Category
Help
j5 Version
2019

This section lists sample SQL statements used by j5 Wonderware Historian Interface to retrieve data from Wonderware Historian. A question mark (?) denotes a parameter passed in.

Read Current Value of Tag from the Live table

SELECT t.TagName, l.vValue, l.DateTime, l.Quality

FROM Runtime.dbo.Tag t

INNER REMOTE JOIN Runtime.dbo.Live l ON t.TagName = l.TagName WHERE l.wwTimezone = 'UTC'

AND t.TagName = ?

Current Value Multiple Reads

SELECT t.TagName, l.vValue, l.DateTime, l.Quality

FROM Runtime.dbo.Tag t

INNER REMOTE JOIN Runtime.dbo.Live l ON t.TagName = l.TagName

WHERE l.wwTimezone = 'UTC'

AND t.TagName IN (?,?,?,?)

Read an archived value (at a certain point time) from the History table

SELECT TagName, vValue, DateTime, Quality

FROM Runtime.dbo.History

WHERE wwTimezone = 'UTC'

AND wwRetrievalMode = 'Delta'

AND DateTime = ?

AND TagName = ?

History Value Multiple Reads

SELECT TagName, vValue, DateTime, Quality

FROM Runtime.dbo.History

WHERE wwTimezone = 'UTC'

AND wwRetrievalMode = 'Delta'

AND DateTime = ?

AND TagName IN (?,?,?,?)

Read an archived value between two timestamps from the History table

SELECT TagName, vValue, DateTime, Quality

FROM Runtime.dbo.History

WHERE wwTimezone = 'UTC'

AND wwRetrievalMode='Delta'

AND DateTime >= ?

AND DateTime <= ?

AND TagName = ?

Calculate Maximum Value of Tag from the History table between two timestamps

  • SQL Server Function

SELECT MAX(Value)

FROM Runtime.dbo.History

WHERE wwTimezone = 'UTC'

AND DateTime >= ?

AND DateTime <= ?

AND TagName = ?

  • AnalogSummary History Table

SELECT TagName, OPCQuality as Quality,

Maximum as vValue, StartDateTime as DateTime, EndDateTime as EndDateTime

FROM Runtime.dbo.AnalogSummaryHistory

WHERE wwTimezone = 'UTC'

AND StartDateTime >= ?

AND EndDateTime <=

AND TagName = ?

Calculate Minimum Value of Tag from the History table between two timestamps

  • SQL Server Function

SELECT MIN(Value)

FROM Runtime.dbo.History

WHERE wwTimezone = 'UTC'

AND DateTime >= ?

AND DateTime <= ?

AND TagName = ?

  • AnalogSummary History Table

SELECT TagName, OPCQuality as Quality, Minimum as vValue, StartDateTime as DateTime,

EndDateTime as EndDateTime

FROM Runtime.dbo.AnalogSummaryHistory

WHERE wwTimezone = 'UTC'

AND StartDateTime >= ?

AND EndDateTime <= ?

AND TagName = ?

Calculate Average Value of Tag from the History table between two timestamps

  • SQL Server Function

SELECT AVG(Value)

FROM Runtime.dbo.History

WHERE wwTimezone = 'UTC'

AND DateTime >= ?

AND DateTime <= ?

AND TagName = ?

This is an event-weighted query.

  • Historian Built in Retrieval Mode

SELECT TagName, Quality, vValue, DateTime

FROM Runtime.dbo.History

WHERE wwTimezone = 'UTC'

AND wwRetrievalMode = 'Average'

AND wwTimeStampRule = 'END'

AND wwCycleCount = 1

AND DateTime > ?

AND DateTime <= ?

AND TagName = ?

This is an event-weighted query.

  • AnalogSummary History Table

SELECT TagName, OPCQuality as Quality, Average as vValue, StartDateTime as DateTime,

EndDateTime as EndDateTime

FROM Runtime.dbo.AnalogSummaryHistory

WHERE wwTimezone = 'UTC'

AND StartDateTime >= ?

AND EndDateTime <=

AND TagName = ?

Retrieval Tag Values for use in Chart and Sparklines

  • Retrieve Interpolated Values

SELECT TagName, vValue, DateTime, Quality

FROM Runtime.dbo.History

WHERE wwTimezone = 'UTC' AND wwRetrievalMode ='Cyclic'

AND wwCycleCount = ?

AND DateTime >= ?

AND DateTime <= ?

AND TagName= ?

Where wwCycleCount parameter passed in is the number of sample points configured in the j5 Designer.

  • Best Fit Retrieval

SELECT TagName, vValue, DateTime, Quality

FROM Runtime.dbo.History

WHERE wwTimezone = 'UTC'

AND wwRetrievalMode ='BestFit'

AND wwCycleCount = ?

AND DateTime >= ?

AND DateTime <= ?

AND TagName= ?

Where wwCycleCount parameter passed in is the number of sample points configured in the j5 Designer.

Write Tag Value back to History Table

INSERT Runtime.dbo.v_History (DateTime, wwTimezone, TagName, Value, Quality, wwVersion)

VALUES (?, 'UTC', ?, ?, 192, 'LATEST')