AnsweredAssumed Answered

How to left outer join query using OleDB Enterprise?

Question asked by rtrudeau on Aug 23, 2016
Latest reply on Aug 23, 2016 by rtrudeau

Hello all,

I'm currently having trouble getting left outer joins working with PI, and OleDB. We're currently using

PI Server 2012, and PI OleDB Enterprise 2016, and within the documentation I've read here:

https://techsupport.osisoft.com/Downloads/File/454246de-1950-4899-a8dd-4462c0031b24

 

On page 25, it outlines syntax for performing a left outer join. As I've read in other documentation,

left joins have been supported as of a few backdated versions of these softwares.

 

I've included a few code samples before of some very simple left join I've attempted. If someone could

point out the error in my query, or provide an alternative simple left join query that would be wonderful.

 

Also note: I must continue to use these softwares, at these specified versions, and transferring to another

technology isn't a feasible solution at this time.

 

/*returns exactly 1 row of data*/

SELECT p1.*

  FROM picomp AS p1

  WHERE p1.time='2016-08-13 09:45:00'

  AND p1.tag='SOME-TAG:NAME'

 

 

/*returns exactly 1 row of data with different column attributes*/

SELECT p2.*

  FROM pimax AS p2

  WHERE p2.time='2016-08-13 09:45:00'

  AND p2.tag='SOME-TAG:NAME'

 

 

/* returns [SQL Parser] [Line 3] Syntax error near 'LEFT'. */

SELECT p1.*, p2.*

  FROM picomp AS p1

  LEFT OUTER JOIN pimax AS p2

  ON p1.time = p2.time

  WHERE p1.time='2016-08-13 09:45:00'

  AND p1.tag='SOME-TAG:NAME'

Outcomes