-
Re: PI AF - Table Lookup Data Reference BETWEEN two values in table
Lal_Babu_ShaikDec 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
-
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
-
-
Re: PI AF - Table Lookup Data Reference BETWEEN two values in table
Jouni Dec 5, 2018 3:20 AM (in response to Mattpdrexel)1 of 1 people found this helpfulSQL 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
-
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.
-
Re: PI AF - Table Lookup Data Reference BETWEEN two values in table
Jouni Dec 5, 2018 7:40 PM (in response to Mattpdrexel)3 of 3 people found this helpfulBETWEEN 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]
-
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!
-
-
Re: PI AF - Table Lookup Data Reference BETWEEN two values in table
Lal_Babu_ShaikDec 6, 2018 12:59 AM (in response to Mattpdrexel)
1 of 1 people found this helpfulHi 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
-
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!
-
-
-