I am trying to get a count of values over a time range using PI SQL Commander. Querying the piarchive..picount table is giving the wrong result, so this is the query I have come up with:
SELECT X.tag, descriptor, COUNT(value) "count"
FROM [pipoint]..[pipoint] X JOIN [piarchive]..[picomp2] Y ON X.tag = Y.tag
WHERE X.tag IN ([...List of Points...]) AND
time BETWEEN '2019-06-18 06:00' AND '2019-06-18 18:00'
GROUP BY X.tag, descriptor
This does what I want very quickly, however it doesn't show a count of 0 for those points without archived values in the time range. To add that, I would like to use a left outer join:
SELECT X.tag, descriptor, COUNT(value) "count"
FROM [pipoint]..[pipoint] X LEFT OUTER JOIN [piarchive]..[picomp2] Y ON X.tag = Y.tag
WHERE X.tag IN ([...List of Points...]) AND
time BETWEEN '2019-06-18 06:00' AND '2019-06-18 18:00'
GROUP BY X.tag, descriptor
But SQL Commander comes back with: [SQL Parser] [Line 2] Syntax error near 'LEFT'.
I've read the PI OLEDB User Guide and this appears to follow the grammar specification, and according to my SQL knowledge this would be correct. Perhaps I am missing something obvious.
HI Trevor,
Unfortunately, OUTER joins are not supported in PI OLEDB Classic Provider which explains the error you are seeing.
--Vince