AnsweredAssumed Answered

PI SQL Commander Prefixes

Question asked by Carson_Ingle on May 8, 2018
Latest reply on May 8, 2018 by Carson_Ingle

I am new to SQL and am comfortable building basic queries, but I am now looking to create more advanced and dynamic queries. My starting point was figuring out how to pull values from multiple tags in the same table with a single query from a Data Archive Server.

 

After searching through the Query Compendium I found the following query that accomplishes that:

 

SELECT i1.tag, i1.time, i1.value, i1.status, i2.tag, i2.value, i2.status

FROM piarchive..piinterp i1

INNER JOIN piarchive..piinterp i2 ON i1.time = i2.time

WHERE i1.tag = 'TAG1' AND i1.time BETWEEN 'y' AND 't'

AND i1.timestep = '1h' AND i2.tag = 'TAG2'

 

I was confused by the query's usage of i1. and i2. At first I thought these were user-defined variables to allow for tag differentiation and joining on time, but when I tried to build a simpler query from scratch with similar "variables" then the query failed.

 

Would anyone happen to know what purpose the above syntax serves? Is the structure specific to JOIN commands? Does the "i" mean anything or could it be substituted with any letter?

 

I am sure this information is out there somewhere and that I just don't know what terms to search for, but I was unable to find any information pertaining to this syntax in the Query Compendium, Live Library, or in generic SQL resources like W3Schools.

Outcomes