Using Query Commands (DSC Module)
- Updated2023-02-21
- 3 minute(s) read
Use data transform and shared variable type case commands to query the Citadel data tables.
Data Transforms
You can include special commands that perform data transforms to manipulate and analyze historical data. You cannot use data transform commands to query raw data tables.
| Command | Transformation |
|---|---|
| MATH_MIN(shared variable | TO_DISCRETE(shared variable) | TO_CONTINUOUS(shared variable)) | Returns the minimum for a shared variable across the interval. |
| MATH_MAX(shared variable | TO_DISCRETE(shared variable) | TO_CONTINUOUS(shared variable)) | Returns the maximum for a shared variable across the interval. |
| MATH_AVG(shared variable | TO_DISCRETE(shared variable) | TO_CONTINUOUS(shared variable)) | Returns the average for a shared variable across the interval. |
| MATH_STDEV(shared variable | TO_DISCRETE(shared variable) | TO_CONTINUOUS(shared variable)) | Returns the standard deviation for a shared variable across the interval. |
| MATH_STARTS(shared variable | TO_DISCRETE(shared variable) | TO_CONTINUOUS(shared variable)) | Returns the number of starts (that is, the number of transitions from OFF to ON) for a shared variable across the interval. For numeric points, Citadel interprets 0.0 as OFF and treats all other numbers as ON. |
| MATH_STOPS(shared variable | TO_DISCRETE(shared variable) | TO_CONTINUOUS(shared variable)) | Returns the number of stops (that is, the number of transitions from ON to OFF) for a shared variable across the interval. For numeric points, Citadel interprets 0.0 as OFF and treats all other numbers as ON. |
| MATH_ETM(shared variable | TO_DISCRETE(shared variable) | TO_CONTINUOUS(shared variable)) | Returns the amount of time a shared variable was in the ON state across the interval. For numeric points, Citadel interprets all nonzero numbers as ON. |
| MATH_QUAL(shared variable | TO_DISCRETE(shared variable) | TO_CONTINUOUS(shared variable)) | Returns the ratio of time for which valid data exist for shared variables across the interval to the length of the interval itself. If valid data exist for only one-half of the interval, MATH_QUAL returns 0.5. You might encounter gaps in the historical data traces in Citadel because of computer shutdown, Shared Variable Engine shutdown, or similar occurrences. |
| RAW_VALUE() | Returns the last raw, or uninterpolated, value before each interval boundary. The Citadel historical database might return raw values for partial intervals. Partial intervals contain at least one data point but have no data at the interval boundary. Therefore, the Citadel historical database does not return an interpolation point. For some partial intervals, the Citadel historical database adds an additional row of data to the query results. This data contains a NULL interpolation result and the last raw datapoint in the trace. This transform can take any trace type as an input. |
| RAW_UTC_TIMESTAMP() | Returns the timestamp, in UTC time, associated with the RAW_VALUE() transform result. This transform is equivalent to the UTCTime column name in the Citadel data tables. This transform can take any trace type as an input. |
| RAW_LOCAL_TIMESTAMP() | Returns the timestamp, in local time, associated with the RAW_VALUE() transform result. This transform is equivalent to the LocalTime column name in the Citadel data tables. This transform can take any trace type as an input. |
You can use the RAW_VALUE(), RAW_UTC_TIMESTAMP(), and RAW_LOCAL_TIMESTAMP() commands in combination to append the last raw point from a trace to a set of interpolated results. You also can use these commands to query raw points from multiple traces. You can use the interpolation interval to control the number of results that the Citadel historical database returns, and you can display the results in a table.
Note that Citadel supports standard set functions (MIN, MAX, AVG, COUNT) and you can use them on any Citadel table. Distinguish between set functions MIN, MAX, AVG and Citadel transform functions MATH_MIN, MATH_MAX, MATH_AVG. Set functions perform calculations on query result. Transform functions perform calculations on interpolation intervals.
Shared Variable Type Cast Commands
You can use type cast commands to override current type of shared variable that you query. You must distinguish between discrete and continuous shared variables because different point interpolation is used for different shared variables. Discrete points are interpolated using step interpolation. Continuous points are interpolated using linear interpolation. You might want to override shared variable type to enforce step or linear interpolation.
| Citadel 5 ODBC driver | Description |
|---|---|
| TO_DISCRETE(project variable) | Cast current shared variable type to discrete (that is, the shared variable is treated as discrete) |
| TO_CONTINUOUS(shared variable) | Cast current shared variable type to continuous (that is, the shared variable is treated as continuous) |
Use these data commands to calculate and retrieve complex information, such as averages and standard deviations, from the database. You do not need to extract the raw data and then manipulate them in another application.