8 Replies Latest reply on Dec 6, 2018 2:00 PM by Mattpdrexel

PI AF - Table Lookup Data Reference BETWEEN two values in table

Mattpdrexel Dec 5, 2018 1:47 AM

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!

Upload.PNG 47.0 KB

Re: PI AF - Table Lookup Data Reference BETWEEN two values in table

Lal_Babu_Shaik Dec 5, 2018 1:59 AM (in response to Mattpdrexel)

Hi Matthew

Correction :

Old :SELECT [Output Value] FROM [Example Table] WHERE [Low Range] <= @[PI AF Attribute] AND [High Range] <= @[PI AF Attribute]

New : SELECT [Output Value] FROM [Example Table] WHERE [Low Range] >= @[PI AF Attribute] AND [High Range] <= @[PI AF Attribute]

Thanks,
Lal
Like Show 0 Likes(0)

Actions
- Re: PI AF - Table Lookup Data Reference BETWEEN two values in table
  
  Mattpdrexel Dec 5, 2018 3:17 PM (in response to Lal_Babu_Shaik)
  
  I still fail to get data back from my query in PI AF when I try this, but thanks for the correction
  
  Like Show 0 Likes(0)
  
  Actions
Re: PI AF - Table Lookup Data Reference BETWEEN two values in table

Jouni Dec 5, 2018 3:20 AM (in response to Mattpdrexel)

SQL Server supports BETWEEN operator which makes the query even easier to read.
SELECT [Output Value] FROM [Example Table] WHERE @[PI AF Attribute] BETWEEN [Low Range] AND [High Range]
More at BETWEEN (Transact-SQL) | Microsoft Docs

1 of 1 people found this helpful
Like Show 0 Likes(0)

Actions
- Re: PI AF - Table Lookup Data Reference BETWEEN two values in table
  
  Mattpdrexel Dec 5, 2018 3:16 PM (in response to Jouni)
  
  Hi Jouni, thanks for the prompt reply.
  
  Unfortunately, it does not look like PI AF recognizes the BETWEEN operator. Within PI AF I receive the following error
  
  "... has thrown an exception: The expression contains unsupported operator 'Between'.
  
  Not sure if this specific search is possible in PI AF.
  
  Like Show 0 Likes(0)
  
  Actions
  - Re: PI AF - Table Lookup Data Reference BETWEEN two values in table
    
    Jouni Dec 5, 2018 7:40 PM (in response to Mattpdrexel)
    
    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]
    3 of 3 people found this helpful
    
    Like Show 1 Likes(1)
    
    Actions
    - Re: PI AF - Table Lookup Data Reference BETWEEN two values in table
      
      Mattpdrexel Dec 6, 2018 2:00 PM (in response to Jouni)
      
      Jouni,
      
      I thought I tried the corrected order but apparently not.
      This works perfectly, thanks a lot guys!
      
      Like Show 1 Likes(1)
      
      Actions
  - Re: PI AF - Table Lookup Data Reference BETWEEN two values in table
    
    Lal_Babu_Shaik Dec 6, 2018 12:59 AM (in response to Mattpdrexel)
    
    Hi Matthew
    
    Did you try correcting your expression ? Both high and low has same comparision in your initial expression. REquest you to correct the same.
    
    Correction :
    
    Old :SELECT [Output Value] FROM [Example Table] WHERE [Low Range] <= @[PI AF Attribute] AND [High Range] <= @[PI AF Attribute]
    
    New : SELECT [Output Value] FROM [Example Table] WHERE [Low Range] >= @[PI AF Attribute] AND [High Range] <= @[PI AF Attribute]
    
    Thanks,
    Lal
    1 of 1 people found this helpful
    
    Like Show 1 Likes(1)
    
    Actions
    - Re: PI AF - Table Lookup Data Reference BETWEEN two values in table
      
      Mattpdrexel Dec 6, 2018 2:00 PM (in response to Lal_Babu_Shaik)
      
      Work with correction, I thought I tested earlier but I didn't.
      
      Thanks!
      
      Like Show 0 Likes(0)
      
      Actions

Go to original post