AnsweredAssumed Answered

PI SQL LEFT OUTER JOIN Syntax Error

Question asked by TrevorHarness on Jun 19, 2019
Latest reply on Jun 19, 2019 by TrevorHarness

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.

Outcomes