SQL Examples (DSC Module)

»Table of Contents

LabVIEW Datalogging and Supervisory Control Module 8.2 Help
August 2006

NI Part Number:
370246E-01

»View Product Info

The following examples are typical query statements. Queries might be more involved, depending on the system requirements.

Aliases Table Example Queries

The following example queries obtain Citadel data from the Aliases table.

SELECT * FROM Aliases

Retrieves all aliases.

SELECT * FROM Aliases WHERE FullName LIKE '%Process_1%'

Retrieves all aliases of Process_1 traces.

SELECT * FROM Aliases WHERE AliasName LIKE 'DS_%'

Retrieves all data set aliases.

SELECT * FROM Aliases WHERE AliasName NOT LIKE 'DS_%'

Retrieves all non data set traces.

IntData Table Example Queries

The following example queries obtain Citadel data from the IntData table.

SELECT * FROM IntData WHERE LocalTime BETWEEN '2001-11-29 17:00:00' AND '2001-11-29 18:00:00' AND IntInterval = '1:0'

Selects data over a specified time at one-minute intervals.

SELECT * FROM IntData WHERE LocalTime BETWEEN '2001-11-29 17:00:00' AND '2001-11-29 18:00:00' AND IntInterval = '1:0' ORDER BY "computername/my_process/pot1@value"

Selects data over a specified time at one-minute intervals and orders rows based on values in column computername/my_process/pot1@value.

SELECT * FROM IntData WHERE LocalTime BETWEEN '2001-11-29 17:00:00' AND '2001-11-29 18:00:00' AND IntInterval = '1:0' ORDER BY "computername/my_process/pot1@value" DESC

Selects data over a specified time at one-minute intervals and orders rows based on values in column computername/my_process/pot1@value in descending order.

SELECT * FROM IntData WHERE LocalTime BETWEEN '2001-11-29 17:00:00' AND '2001-11-29 18:00:00' AND IntInterval = '1:0' AND "computername/my_process/pot1@value" BETWEEN 1.5 AND 5.4 ORDER BY "computername/my_process/pot1@value" DESC

Selects data over a specified time and value interval at one-minute intervals and orders rows based on values in column computername/my_process/pot1@value in descending order.

SELECT MIN("computername/my_process/pot1@value"), MAX("computername/my_process/pot1@value"), AVG("computername/my_process/pot1@value") FROM IntData WHERE UTCTime BETWEEN '2001-11-29 17:00:00' AND '2001-11-29 18:00:00' AND IntInterval = '1:0'

Selects data over a specified time at one-minute intervals and returns minimum, maximum, and average value of the computername/my_process/pot1@value column.

RawData Table Example Queries

The following example queries obtain Citadel data from the RawData table.

SELECT LocalTime, UTCTime, "computername/my_process/pot1@value" FROM RawData WHERE LocalTime BETWEEN '2001-11-29 17:00:00' AND '2001-12-01 17:00:00'

Selects the computername\my_process\pot1.value shared variable data over a specified time.

SELECT LocalTime, UTCTime, "computername/my_process/pot1@value" FROM RawData WHERE LocalTime BETWEEN '2001-11-29 17:00:00' AND '2001-12-01 17:00:00' AND NOT Quality = 0

Selects the computername\my_process\pot1.value shared variable data with bad quality over a specified time.

SELECT LocalTime, UTCTime, "computername/my_process/pot1@value" FROM RawData WHERE LocalTime BETWEEN '2001-11-29 17:00:00' AND '2001-12-01 17:00:00' ORDER BY "computername/my_process/pot1@value" DESC

Selects the computername\my_process\pot1.value shared variable data over a specified time and order result rows in descending order.

SELECT MIN("computername/my_process/pot1@value"), MAX("computername/my_process/pot1@value") FROM RawData WHERE LocalTime BETWEEN '2001-11-29 17:00:00' AND '2001-12-01 17:00:00'

Selects the computername\my_process\pot1.value shared variable data over a specified time and search the minimum and maximum value.

Dataset Tables Example Queries

The following example queries obtain Citadel data from the Dataset tables.

SELECT LocalTime, UTCTime, RunName,"computername/my_process/pot1@value" FROM DS_RawData_My_Dataset WHERE RunName = 'MyRun_1'

Selects the computername\my_process\pot1.value shared variable data over a data set run MyRun_1.

SELECT LocalTime, UTCTime, RunName, "computername/my_process/pot1@value" FROM DS_RawData_My_Dataset WHERE RunName IN ('MyRun_1', 'MyRun_3')

Selects the computername\my_process\pot1.value shared variable data over a data set runs MyRun_1 and MyRun_3.

SELECT LocalTime, UTCTime, RunName, "computername/my_process/pot1@value" FROM DS_RawData_My_Dataset WHERE RunName LIKE 'MyRun_%'

Selects the computername\my_process\pot1.value shared variable data over a data set whose names begin with string MyRun_.

SELECT Min(LocalTime), Max(LocalTime), COUNT("computername/my_process/pot1@value") FROM DS_RawData_My_Dataset WHERE RunName = 'MyRun_3'

Queries for start time, end time and number of points of the MyRun_3 data set run.

Data Transform and Type Cast Command Example Queries

The following example queries obtain Citadel data using Data Transform and Type Cast commands.

SELECT LocalTime, TO_DISCRETE("computername/my_process/pot1@value") FROM IntData WHERE LocalTime BETWEEN '2001-11-29 17:00:00' AND '2001-11-29 18:00:00' AND IntInterval = '1:0'

Selects the computername\my_process\pot1.value shared variable data over a specified time at one-minute intervals and treats shared variable as discrete.

SELECT LocalTime, MATH_MIN("computername/my_process/pot1@value"), MATH_MAX("computername/my_process/pot1@value") FROM IntData WHERE LocalTime BETWEEN '1999-03-06' AND '1999-03-13' AND IntInterval = '1.0'

Selects the computername\my_process\pot1.value shared variable data over a specified time at one-day intervals and return minimum and maximum day values.

SELECT LocalTime, MATH_MIN("computername/my_process/pot1@value") AS 'min_value' FROM IntData WHERE LocalTime BETWEEN '1999-03-06' AND '1999-03-13' AND IntInterval = '1.0' ORDER BY min_value DESC

Selects the computername\my_process\pot1.value shared variable data over a specified time at one-day intervals and return minimum day values and order results in descending order.

SELECT LocalTime, MATH_MIN(TO_DISCRETE("computername/my_process/pot1@value")) WHERE LocalTime BETWEEN '1999-03-06' AND '1999-03-13' AND IntInterval = 'WEEK'

Selects the computername\my_process\pot1.value shared variable data over a specified time at week intervals and return minimum week values. Note that the computername\my_process\pot1.value shared variable is treated as a Boolean shared variable.