Some useful SQL for OLEDB at gYTHIALY’s Blog

November 14, 2006 No comments

Some useful SQL for OLEDB

Get snapshot for all tags

SELECT tag, time, value, DIGSTRING(status) AS status FROM piarchive..pisnapshot

Get data for a digital tag

SELECT tag, time, DIGSTRING(CAST(value AS Int32)) AS value, DIGSTRING(status) AS status
FROM piarchive..picomp2
WHERE tag = 'cdm158' AND time >= 't'

Get data for a tag list

SELECT tag, time, value, DIGSTRING(status) AS status FROM piarchive..picomp2
WHERE tag IN (SELECT tag FROM pipoint..classic WHERE pointsource = 'R') AND time BETWEEN 'y' AND 't'

Get data for a tag list, with handling of digital states and bad statuses

SELECT s.tag, time, CASE WHEN status = 0 THEN (CASE WHEN p.pointtype = 'R' OR p.pointtype = 'I' OR p.pointtype = 'S' THEN value ELSE DIGSTRING(CAST(value AS Int32)) END) ELSE DIGSTRING(status) END AS value FROM piarchive..pisnapshot s, (SELECT tag, pointtype FROM pipoint..classic WHERE pointsource = 'R') p WHERE s.tag = p.tag

Note: the usage of the CASE WHEN..THEN..ELSE..END conditional statement, which act exactly like an IF..THEN..ELSE statement, but in SQL queries

Get annotated events for a tag

SELECT time, value, annotations FROM piarchive..picomp2
WHERE tag = 'sinusoid' AND time >= '*-1mo' AND annotated = TRUE

Write a new snapshot value for a digital tag

INSERT INTO piarchive..picomp2 (tag, value)
VALUES ('cdm158', DIGCODE('Cascade', 'Modes'))

Write an archive value in the past

INSERT INTO piarchive..picomp2 (tag, time, value)
VALUES ('sinusoid', '*-1h', 5000)

Write an archive value with an annotation and the questionable flag

INSERT INTO piarchive..picomp2 (tag, time, value, annotations, questionable)
VALUES ('sinusoid', 'y+8h', 5000, 'Reason for special value…', TRUE)

Multiply values of a tag by 2

UPDATE piarchive..picomp2 SET value = CAST(value AS Float32) * 2
WHERE tag = 'sinusoid' AND time BETWEEN '*-2.5h' AND '*-2h'

Delete values for a given tag

DELETE FROM piarchive..picomp2
WHERE tag = 'cdt158' AND time BETWEEN '*-8h' AND '*-7h

Get synchronized averages for 2 tags

SELECT a1.time, a1.value AS sinusoid_avg, a2.value AS sinusoidu_avg FROM piarchive..piavg AS a1
INNER JOIN piarchive..piavg AS a2 ON a1.time = a2.time AND a1.timestep = a2.timestep
WHERE a1.time BETWEEN 'y' AND 't' AND a1.timestep = '1h'
AND a1.tag = 'sinusoid' AND a2.tag = 'sinusoidu'

Get Performance Equation tags (PointSource = C) whose snapshot value is:

SELECT tag FROM piarchive..pisnapshot
WHERE tag IN (SELECT tag FROM pipoint..classic WHERE pointsource = 'C')
AND ((DATE('*') - time) BETWEEN RELDATE('4h') AND RELDATE('365d')
OR status <> 0)

Get List of tags

SELECT tag, descriptor FROM pipoint..classic
WHERE pointsource = 'R'

Count tags by PointSource attribute

SELECT pointsource, COUNT(*)
FROM pipoint..classic GROUP BY pointsource

Create a tag

INSERT pipoint..classic (tag, pointtypex)
VALUES ('TestTag', 'float32')

Rename a tag

UPDATE pipoint..classic SET tag = 'TestTag2'
WHERE tag = 'TestTag'

Delete a tag

DELETE FROM pipoint..classic
WHERE tag = 'TestTag2'

List all digital state sets

SELECT DISTINCT(digitalset) FROM pids..pids

Create a Digital State Set

CREATE TABLE pids..TestSet (code Int32, offset Int32, name WString)

Create States in a Digital State Set

INSERT INTO pids..TestSet (name) VALUES ('TestState1')

List all states in a specific digital state set

SELECT name FROM pids..TestSet

Rename a Digital State

UPDATE pids..TestSet SET name = 'TestState3'
WHERE name = 'TestState1'

Delete a Digital State Set

DROP TABLE pids..TestSet

Get Sub-Modules of a specific Module

SELECT name FROM pimodule..pimoduleh
WHERE path = '\SomeTopModule\'

Get Modules under a specific Branch

SELECT name FROM pimodule..pimoduleh
WHERE path LIKE '\-%OSI\%' ESCAPE '-'

Use the ESCAPE statement to specify the first % character is textual while the second one corresponds to the wildcard character

Get Module Aliases

SELECT name, '\\' + server + '\'+ tag AS tag FROM pimodule..pialias WHERE moduleuid = MODULEUID('\Some Module\SubModule')

The server and tag columns should be looked at together, since aliases can come from different PI Servers

Get Module Properties

SELECT path, name, value FROM pimodule..piproperty
WHERE moduleuid = MODULEUID('\Some Module\SubModule)

The path column should be considered, since properties can be hierarchic

Create a Module at root of ModuleDB

INSERT INTO pimodule..pimoduleh (name)
VALUES ('TestModule')

Create a Sub-Module in a Module

INSERT INTO pimodule..pimoduleh (name, path)
VALUES ('TestSubModule', '\TestModule\')

Mark a Module as Unit (for UnitBatch support)

UPDATE pimodule..pimodule SET ispiunit = TRUE
WHERE uid = MODULEUID('\TestModule')

Create a Module Alias

INSERT pimodule..pialias (moduleuid, moduleeffective, name, tag)
VALUES ( MODULEUID('\TestModule\TestSubModule'), MODULEEFFECTIVE(MODULEUID('\TestModule\TestSubModule')), 'TestAlias', 'sinusoid')

Create a Module Property

INSERT pimodule..piproperty (moduleuid, moduleeffective, name, value)
VALUES ( MODULEUID('\TestModule\TestSubModule'), MODULEEFFECTIVE(MODULEUID('\TestModule\TestSubModule')), 'TestProperty', 11)

There is no type column in the “piproperty” table. Use the CAST(Value AS Type) to explicitly specify the type

Move a Module in the Hierarchy

UPDATE pimodule..pimoduleh
SET path = '\%OSI\' WHERE path = '\TestModule\' AND name = 'TestSubModule'

Permanently Delete a Module

DELETE pimodule..pimodule
WHERE uid = MODULEUID('\%OSI\TestSubModule')

Get Unit Batches Made in a Unit

SELECT batchid, starttime, endtime, product FROM pibatch..piunitbatch
WHERE moduleuid = MODULEUID('\Some Company\Some Division\Units A\Unit A1')
AND starttime >= 't' ORDER BY starttime

Get Batches with its UnitBatches and SubBatches

SELECT b.batchid AS Batch, u.batchid AS UnitBatch, u.starttime AS UBStart, u.endtime AS UBEnd
FROM pibatch..pibatch b INNER JOIN pibatch..piunitbatch u ON u.batchuid = b.uid
WHERE b.starttime > '*-1d' AND b.batchid = 'TestBatch' ORDER BY Batch, UBStart

Create a Unit Batch, linked to a Batch

INSERT INTO pibatch..piunitbatch (batchid, starttime, moduleuid, batchuid)
VALUES ('TestUnitBatch', '*', MODULEUID('\TestModule'), (SELECT uid FROM pibatch..pibatch
WHERE batchid = 'TestBatch' AND starttime >= 't'))

End a Unit Batch