wwtools/histdb/02-syntax-limits.md

SQL syntax — what works, what doesn't

The INSQL OLE DB provider supports a subset of T-SQL. Knowing the cliffs in advance saves a lot of "mysteriously empty rowset" debugging.

Quick capability matrix

Syntax element	Four-part / view	OPENQUERY
ORDER BY	✅	❌ inside the body. Wrap and order outside.
GROUP BY	✅	❌
TagName IN (...)	✅	✅
TagName LIKE '...'	✅	✅
Date/time functions (DateAdd, GETDATE, …)	✅	✅
MIN, MAX, AVG, SUM, STDEV	All	MIN, MAX, AVG, SUM only — no STDEV
Sub-SELECT (one normal SQL Server table + one extension table)	✅ with restrictions	❌
Sub-SELECT (two extension tables)	❌	❌
Variables (@foo)	✅ outside OPENQUERY	❌ inside

Time-domain extensions (the wwXxx columns)

The Historian exposes "virtual columns" on every extension table that double as input parameters. They control retrieval semantics:

wwCycleCount       wwEdgeDetection    wwFilter           wwInterpolationType
wwOption           wwQualityRule      wwResolution       wwRetrievalMode
wwStateCalc        wwTimeDeadband     wwTimeStampRule    wwTimeZone
wwValueDeadband    wwValueSelector    wwVersion

Reserved / deprecated: wwParameters, wwMaxStates (reserved), wwRowCount (deprecated — use wwCycleCount).

Set them in the WHERE:

SELECT DateTime, Value
FROM History
WHERE TagName = 'SysTimeSec'
  AND DateTime >= '2001-12-02 10:00:00'
  AND DateTime <= '2001-12-02 10:02:00'
  AND wwResolution    = 10
  AND wwRetrievalMode = 'cyclic';

Three rules that bite:

1. The provider is stateless. Every extension parameter must be set in every query that needs it — there is no session memory.
2. IN and OR cannot multiplex an extension. wwVersion IN ('original','latest') and wwRetrievalMode = 'Delta' OR wwVersion = 'latest' both fail.
3. Values are case-insensitive even on a case-sensitive SQL Server collation.

Each individual extension is documented in 04-retrieval-options.md.

LIKE — only TagName and Value

LIKE only works on the TagName and Value columns (and Value LIKE only against a string tag).

SELECT TagName, Value
FROM History
WHERE TagName LIKE 'Sys%'
  AND DateTime > '1999-05-24 14:30:00'
  AND DateTime < '1999-05-24 14:32:00';

IN against AnalogTag / DiscreteTag / StringTag with LIKE

If you push a LIKE inside an IN-driven sub-SELECT against the typed tag tables, the query fails unless you select the vValue column. An INNER REMOTE JOIN is more efficient anyway:

-- Clunky (and only works if vValue is in the projection):
SELECT DateTime, TagName, vValue, Quality, QualityDetail
FROM History
WHERE TagName IN (SELECT TagName FROM StringTag WHERE TagName LIKE 'SysString')
  AND DateTime BETWEEN '2001-06-21 16:00:00.000' AND '2001-06-21 16:40:00.000'
  AND wwRetrievalMode = 'Delta';

-- Preferred:
SELECT h.TagName, DateTime, Value
FROM SnapshotTag st
INNER REMOTE JOIN INSQL.Runtime.dbo.History h
  ON st.TagName = h.TagName
WHERE EventTagName = 'SysStatusEvent'
  AND DateTime = '2001-12-20 0:00';

Joins

• Inside OPENQUERY: not supported. Both implicit (FROM Tag t, Live v) and explicit (JOIN) joins fail. Always pull the join out:

  SELECT v.DateTime, v.TagName, v.Value, e.Unit
  FROM OPENQUERY(INSQL, '
    SELECT DateTime, TagName, Value FROM Live
    WHERE TagName LIKE "%Date%"
  ') v
  JOIN AnalogTag t      ON v.TagName = t.TagName
  JOIN EngineeringUnit e ON t.EUKey   = e.EUKey
  ORDER BY t.TagName;
  

• Joining a SQL Server table against an extension table: use INNER REMOTE JOIN with the SQL Server table on the left and the extension table on the right:

  <SQLServerTable> INNER REMOTE JOIN <HistorianExtensionTable>
  

  This forces SQL Server to send the join predicate down to the OLE DB provider in one round trip rather than dragging the whole extension table back to the engine.

CONVERT on vValue inside OPENQUERY

CONVERT(...) against the variant vValue column is not supported inside the OPENQUERY body. Filter outside:

SELECT * FROM OPENQUERY(INSQL, '
  SELECT DateTime, Quality, OPCQuality, QualityDetail, Value, vValue, TagName
  FROM History
  WHERE TagName IN ("SysTimeMin", "SysPulse")
    AND DateTime >= "2001-12-30 04:00:00.000"
    AND DateTime <= "2001-12-30 09:00:00.000"
    AND wwRetrievalMode = "Delta"
') 
WHERE CONVERT(float, vValue) = 20.0;

Sub-SELECTs against a SQL Server table + extension table

Permitted but inefficient — SQL Server fans the whole sub-query out badly. Always rewrite as INNER REMOTE JOIN. Two-extension-table sub-SELECTs are not supported at all.

Optimizer dropping criteria

The SQL Server optimizer sometimes decides a WHERE clause is "redundant" and never sends it to INSQL, producing wrong-but-not-erroring results. Diagnose with the SSMS query plan; remediate by rewriting so the criteria flow through a more direct path, e.g. put the small driver table on the left of INNER REMOTE JOIN so its tagnames flow into the right side:

DECLARE @TagList TABLE (TagName nvarchar(256));
INSERT @TagList SELECT 'SysTimeSec' UNION SELECT 'SysPerfCPUTotal';

-- Wrong: TagName criteria gets optimized out
SELECT DateTime, h.vValue, h.TagName
FROM History h
INNER REMOTE JOIN @TagList l ON h.TagName = l.TagName
WHERE DateTime >= DATEADD(hour, -1, GETDATE()) AND DateTime < GETDATE()
  AND wwRetrievalMode = 'AVG' AND wwCycleCount = 1;

-- Right: driver table on the left, criteria reach the OLE DB provider
SELECT DateTime, h.vValue, h.TagName
FROM @TagList l
INNER REMOTE JOIN History h ON h.TagName = l.TagName
WHERE DateTime >= DATEADD(hour, -1, GETDATE()) AND DateTime < GETDATE()
  AND wwRetrievalMode = 'AVG' AND wwCycleCount = 1;

Variant columns + functions

If you wrap a variant column (vValue) with a function like ROUND(), SQL Server emits a syntax error that doesn't propagate back through OLE DB — you get silently wrong / empty rows. Cast first or filter on the typed Value column.

StartDateTime cannot drive query criteria

Many retrieval modes return a StartDateTime for the cycle. You can SELECT it, but you cannot use it in WHERE — the predicate is silently ignored. Use DateTime only.

Comparisons and NULL

A predicate like Value > 0 will error when the provider returns a NULL. Always pair it with AND Value IS NOT NULL.

WHERE clause anomalies

If the SQL Server optimizer decides your WHERE is "useless" (typically a LIKE '%' against TagName), it removes the clause entirely and you get the OLE DB error from 01-overview.md. Add another harmless predicate to keep the clause "needed":

SELECT DateTime, Value, QualityDetail
FROM History
WHERE TagName LIKE '%'
  AND wwRetrievalMode = 'delta';   -- forces the WHERE to survive optimization

Compatibility notes for the older datetime type

If your client uses the older SQL Server datetime type (instead of datetime2), expect rounding to ~3.3 ms vs. the 100 ns native FILETIME resolution.

What OPENQUERY and Microsoft Query do not talk

Microsoft Query (the legacy Excel/Access tool) cannot parse OPENQUERY statements. Use one of the other connection paths from Excel.

Next

• 03-retrieval-modes.md — every wwRetrievalMode value.
• 04-retrieval-options.md — every other wwXxx parameter.