I am interested in creating a PI AF Attribute that returns the value of a column when a certain PI Point Attribute is between two values from a reference Table.
For example, for the table below, I'd like to return the appropriate value from [Output Value] for a given PI AF Attribute input.
Here, the PI Point is called as @[PI AF Attribute], which is dynamic and will change continuously.
Attempted Query:
SELECT [Output Value] FROM [Example Table] WHERE [Low Range] <= @[PI AF Attribute] AND [High Range] <= @[PI AF Attribute]
For example, if @[PI AF Attribute] were to equal 8.5, I'd expect the output to be 5.
| Low Range | High Range | Output Value |
|---|---|---|
| 0 | 3 | 4.6 |
| 3 | 6 | 4.7 |
| 6 | 10 | 5 |
| 9 | 12 | 4.6 |
However, the PI AF SQL type query returns 'None' as an output when I attempt this within PI AF for my real data (see attached).
I am not sure how to achieve this table lookup given.
Any suggestions would be highly appreciated.
I understand this would be simpler with Interpolation, but I am trying to avoid that.
Thanks!
BETWEEN operator is handy, but same results can be achieved with more traditional SQL.
Looking at Lal's response, I think greater than / less than operators are wrong way round and should be:
SELECT [Output Value] FROM [Example Table] WHERE [Low Range] <= @[PI AF Attribute] AND [High Range] >= @[PI AF Attribute]
That givse you rows where [PI AF Attribute] is greater than [Low Range] and less than [High Range]
Another way of putting it:
SELECT [Output Value] FROM [Example Table] WHERE @[PI AF Attribute] >= [Low Range] AND @[PI AF Attribute] <= [High Range]