Can an Easy Chart DB Pen Plot a Custom Decimated SQL Query?

Query Ownership in an Easy Chart DB Pen

An Easy Chart DB pen does not run a query you write. You give it a table name, a value column, a timestamp column and an optional WHERE fragment. The component then builds its own SELECT and adds a timestamp range filter from the chart's current start and end dates. Paste a full statement such as SELECT * FROM (SELECT ... ROW_NUMBER() OVER ...) x WHERE x.count % 2 = 0 into the pen's SQL field and the component wraps it inside its own generated query. The result is invalid SQL, or a pen that never returns rows.

That wrapping is why a custom decimation query fails on a DB pen with no useful error. The pen expects a table-shaped source and cannot accept an arbitrary result set. There are two ways around it: give the pen a table-shaped object that already contains the decimation, or move to a component whose data you bind directly to a query.

Row Stride Against Rows per Timestamp

Before choosing a component, check the query itself. The number that matters is rows per timestamp. In the sample output, even-numbered rows 2 through 16 all carry 2013-11-07 20:00:00.000, and row 18 starts 20:10:00.000. That means 16 rows share each timestamp. The values also repeat: row 2 (-6.4999) matches row 18 (-6.4999), and row 4 (1558.476) matches row 20 (1556.476). This is a tall table in which each timestamp holds one row per channel.

Two consequences follow:

• Ties in the ORDER BY. ROW_NUMBER() OVER (ORDER BY myDate) gives 16 tied rows per timestamp. The database can number tied rows in any order, and that order can change between executions or after an index rebuild. % 2 = 0 then keeps a different set of 8 channels on different runs.
• Stride and channel count. With 16 rows per timestamp, stride 2 keeps 8 channels per timestamp. Stride 3 does not divide 16, so the selected channels rotate at every timestamp (derived from 16 mod 3 = 1). Each plotted series then becomes a mix of different channels.

If the goal is fewer points per channel over time, partition the numbering by channel. If the goal is fewer channels, filter by channel and do not use row numbers at all. The column that identifies the channel is not in the sample output. The code below uses seriesId as a placeholder for it.

-- T-SQL style; % is the modulo operator
SELECT myDate, myValue
FROM (
  SELECT myDate, myValue,
         ROW_NUMBER() OVER (PARTITION BY seriesId ORDER BY myDate) AS rn
  FROM myTable
  WHERE seriesId = 1          -- one channel per pen/series
) x
WHERE x.rn % 2 = 0
ORDER BY myDate;

Approaches Compared

You can get the decimated result onto a chart in three ways. Tags are not a data source for the history itself. They are useful as parameters inside the query, for example a tag that holds the stride or the channel number.

Recommended Path: Classic Chart Data Binding

Use a Classic Chart with a SQL query binding on its Data property. This setup is confirmed to plot the decimated result. It also gives you full control over where the date filter sits relative to ROW_NUMBER(), and that choice changes which points appear.

The view approach is the right choice when the operator workflow depends on Easy Chart features such as pen selection and interactive zoom. Its limit is that the Easy Chart's range filter is applied outside the view. The numbering therefore runs over every row in the table on every refresh, and the chart only trims the output afterwards.

Put the filter inside for trending screens that refresh often. Put it outside only when points must line up exactly across different windows, and keep the table small or archived.

Blank Chart Diagnostics

A Classic Chart that shows nothing and reports no error almost always received a dataset in a shape it cannot plot. It does not mean the query failed. The chart reads the first column as the X axis and every column after it as a series.

Binding Procedure With a Date Range Component

The Classic Chart has no start date or end date field. The time window exists only in the WHERE clause of the binding, so the range goes into the query text.

1. Place a Classic Chart and a Date Range component on the same window.
2. Open the binding editor on the Classic Chart's Data property and choose a SQL query binding against the correct database connection.
3. Enter the query with the timestamp column first and no row-number column in the outer SELECT.
4. Place the cursor where the start date goes. Use the property-browse button on the right side of the query dialog to insert the Date Range component's start-date property. Repeat for the end date. Use the tag-browse button in the same place if the range or stride comes from tags.
5. Enclose each inserted reference in single quotes. The binding substitutes the value as text, and an unquoted date string is a SQL syntax error.
6. Set the binding's polling to match how fast rows arrive. With 10-minute sample spacing, fast polling adds load and no new points.
7. Close the editor and check the dataset in the designer before changing chart appearance.

SELECT myDate, myValue
FROM (
  SELECT myDate, myValue,
         ROW_NUMBER() OVER (PARTITION BY seriesId ORDER BY myDate) AS rn
  FROM myTable
  WHERE seriesId = 1
    AND myDate BETWEEN '{<Date Range start property>}'
                   AND '{<Date Range end property>}'
) x
WHERE x.rn % 2 = 0
ORDER BY myDate

The <...> tokens are placeholders. Replace them with the exact paths the property browser inserts. Do not type the paths by hand. To make the stride adjustable, replace the literal 2 with a reference to a numeric property or tag. A number needs no quotes.

For the view alternative, create the view once on the database. Then point the Easy Chart DB pen at the view name as its table, with myDate as the timestamp column and myValue as the value column:

CREATE VIEW myTable_decimated AS
SELECT myDate, myValue, seriesId
FROM (
  SELECT myDate, myValue, seriesId,
         ROW_NUMBER() OVER (PARTITION BY seriesId ORDER BY myDate) AS rn
  FROM myTable
) x
WHERE x.rn % 2 = 0;

Keep seriesId in the view so each pen's WHERE fragment can select one channel.

Verification Quantities

Check the result against counts. A chart that looks right can still be showing the wrong subset of rows.

Move the Date Range handles and confirm three things: the row count scales with the window width, the first timestamp follows the range start, and the query duration stays flat. If duration grows with table size rather than window size, the date filter is sitting outside the numbering, or myDate has no index. This is database load, not chart logic.

FAQ