QUANTITY_CONVERT
Description
This operator converts quantity units based on a QUANTITY_CONVERSION_RATES table, which is optionally provided by the user. If not provided by the user, a default QUANTITY_CONVERSION_RATES table will be used.
The conversion rate is taken from the QUANTITY_CONVERSION_RATES table based on the original unit of the item, the ID of the item, and the requested unit to convert to. The output of the operator is a new column containing the values of the converted unit amounts.
The QUANTITY_CONVERSION_RATES table should have the following columns:
These column names are case insensitive. Furthermore, the conversion rate is taken to be valid for only rows which are unique in the combination [ConversionQualifier, FromUnit, ToUnit] if they are trying to be matched.
Syntax
QUANTITY_CONVERT ( amount, FROM(from_unit), TO(to_unit) [, identifier, [ quantity_conversion_rates ] ] )
amount: The original amount we want to convert, must be a constant or a column of type FLOAT or INT.
from_unit: The unit of the original amount, must be a constant or a column of type STRING.
to_unit: The requested unit to convert to, must be a constant or a column of type STRING.
identifier: The identifier of the original quantity, must be a constant or a column of type STRING.
quantity_conversion_rates: The table containing the conversion rates. It must contain the columns as described above. If not specified, "QUANTITY_CONVERSION_RATES" is taken as the default name for the conversion rates table. This table should be present in the datamodel, but it is not required to be joined to any other table.
Note: At least one of the expressions amount, from_unit, to_unit, and, if present, identifier must be a column of type STRING, and they must share a common table.
Behavior details
A NULL-value is returned if no conversion rate can be found in the QUANTITY_CONVERSION_RATES table for the given to_unit, from_unit and ident parameters.
NULL handling
If an entry in the amount, to_unit or from_unit column is NULL, the corresponding result in that row will also be NULL.
Examples
[1] An example of using the short syntax of the operator. The default name for the QUANTITY_CONVERSION_RATES table is used: | ||||||||||||||||||||||||||||||||||||
| ||||||||||||||||||||||||||||||||||||
|
[2] An example of converting two different units in 'ITEMS'-units with a given quantity-conversion-rates table 'QCR': | ||||||||||||||||||||||||||||||||||||
| ||||||||||||||||||||||||||||||||||||
|
[3] An example in which the ID of the input quantity is not listed in the QUANTITY_CONVERSION_RATES table. In this case, for that ID a NULL-value is returned: | ||||||||||||||||||||||||||||||||||||
| ||||||||||||||||||||||||||||||||||||
|
[4] An example demonstrating the behaviour of QUANTITY_CONVERT when Amount, fromUnit and ToUnit are constant and only the IDs are not: | ||||||||||||||||||||||||||||||||
| ||||||||||||||||||||||||||||||||
|