What is Cross Apply?

Document created by kduffy Employee on Feb 14, 2019Last modified by kduffy Employee on Feb 14, 2019
Version 3Show Document
  • View in full screen mode

Cross Apply is seen in the query compendium and example queries in a number of locations within PI SQL Client and PI OLEDB Enterprise, but since it's not a widely used operator, many people are confused by its usage.

 

While a JOIN appends columns from a second table onto existing rows from the first table, a CROSS APPLY applies a function to every row in the table.

 

There are many third party websites that describe the CROSS APPLY operator from a general sense, and provide examples, such as the following:

Note: These are external websites that are being linked as-is. OSIsoft takes no responsibility for their content

 

From the perspective of the PI System, we'll look at the above query.

 

In this example, the GetSampleValue table-valued function was cross applied to the attribute table. If the operator were a JOIN, then the GetSampleValue would need to be a table or view with columns to associate with the attributeID. These columns would be appended to the attribute table, widening the result set.

 

But GetSampleValue is a function, so it can't be joined like this. Instead, it takes the attribute ID and a timestamp as input parameters and it outputs the value of the input attribute (among other columns) at that time

 

We can demonstrate this process by getting the attribute ID directly, and manually inputting it into the function, noticing that the function does not inherently require CROSS APPLY to be used.

 

But if we would like to APPLY the function aCROSS the entire attribute table being supplied, then the SQL query engine will perform that operation for us with the CROSS APPLY operator:

Attachments

    Outcomes