between(X;Y;Z)
Returns a boolean value indicating whether a given value is between two other values.
Syntax
between(X;Y;Z)
Input
Argument | Type | Description |
---|---|---|
X |
any simple type | The value to test within the range defined by Y and
Z |
Y |
any simple type | The minimum value of the range in which to check for X Note: The order of
Y and Z doesn't matter.If |
Z |
any simple type | The maximum value of the range in which to check for X Note: The order of
Y and Z doesn't matter.If |
Note: All arguments must be of the same type. This function is most commonly used with
numeric values; however, if strings are provided as arguments, the comparison is done as a
binary string comparison.
Return Value
Returns an integer value of 1 if either:
X
>=Y
andX
<=Z
X
>=Z
andX
<=Y
If X
is N/A, the result is 0, unless either
Y
or Z
(or both) are N/A, in which case the result is
1.
If X
, Y
, and Z
are not either all
numeric values or all string values, an error is returned.
Sample Usage
value |
begin |
end |
between(value;begin;end) |
---|---|---|---|
2 | 1 | 3 | 1 |
2 | 3 | 1 | 1 |
1.5 | 1 | 3 | 1 |
1.5 | 2.5 | 3.1 | 0 |
1.5 | 1.2 | 3.1 | 1 |
'bat' | 'bar' | 'baz' | 1 |
'bag' | 'bar' | 'baz' | 0 |
'foo' | 'bar' | 'baz' | 0 |
1 | 3 | 0 | |
1 | 1 |
Example
In the "Sales Item Detail" table (pub.demo.retail.item), you can find only those rows whose values in the Sales column fall between 1 and 2.25. To do this, create a computed column and apply thebetween(X;Y;Z)
function to the sales
column, where
Y
is 1 and Z
is
2.25:<note type="base">Applied to table: pub.demo.retail.item</note> <willbe name="between_result" value="between(sales;1;2.25)"/>
For
those values in the sales
column that fall between 1 and 2.25, the result
is 1. Otherwise, the result is
0.