IH：ihRawData Examples

ihRawData Examples
The ihRawData table can generate a large number of rows if not used with caution. You can easily generate queries which take a very long time to complete and put stress on the archiver and generate network traffic.

Tasks that you might want to perform on the ihRawData table are outlined in the following examples.

Example 1: Retrieving All Samples Where a Value Was Outside the Query Supplied Values
SELECT * FROM ihRawData WHERE value<140000 OR value>150000

Example 2: Retrieving All Bad Samples (Raw Data)
SELECT * FROM ihRawData WHERE quality NOT LIKE good*

AND samplingmode=RawbyTime

Example 3: Counting the Number of Bad Samples (Raw Data)
SELECT COUNT() FROM ihRawData WHERE quality NOT LIKE good

AND samplingmode=RawbyTime

Example 4: Retrieving All Bad Samples Over the Last Day (Interpolated Data)
SELECT timestamp, tagname, value, quality FROM ihRawData

WHERE samplingmode=rawbytime

AND Quality NOT LIKE good*

AND timestamp>=Now-24H

Example 5: Using an Explicit Time Zone
SELECT * FROM ihRawData WHERE timezone=300

Example 6: Performing a Simple Sequence of Events
SELECT timestamp, tagname, value, quality FROM ihrawdata

WHERE samplingmode=rawbytime ORDER BY timestamp

Example 7: Reporting the Busiest Tags
SELECT tagname, value FROM ihRawData

WHERE samplingmode=calculated

AND calculationmode=count

AND numberofsamples=1

AND timestamp>=‘07/30/2002 10:00:00’

AND timestamp<=‘07/30/2002 11:00:00’ order by value descending

Example 8: Retrieving All Bad Samples Over the Last Day
SELECT timestamp, tagname, value, quality FROM ihRawData

WHERE samplingmode=rawbytime

AND Quality NOT LIKE good*

AND timestamp>=Now-24H

Example 9: Retrieving All Bad Samples, Ignoring End of Collection Markers
SELECT timestamp, tagname, value, quality FROM ihRawData

WHERE samplingmode=rawbytime

AND Quality NOT LIKE good*

AND quality NOT LIKE ‘bad offline’

AND timestamp>=Now-24H

Example 10: Counting the Number of Bad Samples, Ignoring End of Collection Markers
SELECT COUNT(*) FROM ihRawData WHERE samplingmode=rawbytime

AND Quality NOT LIKE good* and quality NOT LIKE ‘bad offline’

AND timestamp>=Now-24H

Example 11: Obtaining All Raw Samples from Yesterday with a Comment
SELECT ihRawData.Tagname, ihRawData.TimeStamp, ihRawData.Value

FROM ihRawData

INNER JOIN ihComments ON ihComments.Tagname = ihRawData.Tagname

AND ihComments.Timestamp = ihRawData.Timestamp

AND ihComments.Comment = “The comment”

WHERE samplingmode=rawbytime

AND ihComments.Timestamp > Yesterday

AND ihComments.Timestamp < Today

Example 12: Determining the Number of Milliseconds Per Interval with Good Data
SELECT timestamp, tagname, value as TimeGood, quality, intervalmilliseconds FROM ihRawData

WHERE tagname=Denali.Simulation00001

AND samplingmode=calculated

AND calculationmode=timegood

AND intervalmilliseconds=10s

AND timestamp>=‘1/20/2003 13:18:00’

AND timestamp<=‘1/20/2003 13:20:00’

Example 13: Retrieving Raw Minimum and Maximum Values per Interval
In this example, you use the data retrieved from the query (with the Trend sampling mode) for plotting points.

SELECT timestamp, tagname, value, quality

FROM ihRawData

WHERE tagname=dFloatTag5

AND samplingmode=trend

AND intervalmilliseconds=24h

AND timestamp>=‘1/01/2003 07:00:00’

AND timestamp<=‘1/10/2003 12:00:00’

Example 14: Retrieving Data with Native Values if the Tag is associated with an Enumerated Set
If enumsetrawvalue is set False, the data is retrieved with string values by default. If enumsetrawvalue is set to True, the raw values are retrieved. Once set, these values are retrieved by default for the current session and will only change when you open a new session.

SELECT * from ihrawdata

WHERE samplingmode=‘rawbytime’ and tagname=mytag

and enumsetrawvalue=TRUE

SELECT timestamp,value,quality from ihrawdata WHERE tagname = MyTag

and samplingmode=Interpolated and numberofsamples=6 and criteriastring=‘enumnativevalue’

SET enumsetrawvalue=’TRUE’

SELECT * from ihrawdata

WHERE samplingmode=‘rawbytime’ and tagname=mytag

Example 15: Retrieving Average Values for Enumerated Set
SET enumsetrawvalue=’FALSE’

SELECT * from ihrawdata

WHERE tagname like Call and samplingmode=calculated

and calculationmode=average