Arithmetic Expressions

Syntax

add_expression ::= multiply_expression (("+"|"-") multiply_expression)*

multiply_expression ::= unary_expression (("*"|"/"|"div") unary_expression)*

unary_expression ::= path_expression | (("+"|"-") unary_expression)

Semantics

Oracle NoSQL Database supports the following arithmetic operations: +, -, *, / and div. Each operand to these operators must produce at most one numeric item. If any operand returns the empty sequence or NULL, the result of the arithmetic operation is also empty or NULL, respectively. Otherwise, the operator returns a single numeric item, which is computed as follows:

If any operand returns a Number item, the item returned by the other operand is cast to a Number value (if not a Number already) and the result is a Number item that is computed using java's arithmetic on BigDecimal, otherwise,
If any operand returns a double item, the item returned by the other operand is cast to a double value (if not a double already) and the result is a double item that is computed using java's arithmetic on doubles, otherwise,
If any operand returns a float item, the item returned by the other operand is cast to a float value If not a float already) and the result is a float item that is computed using java's arithmetic on floats, otherwise,
Except for the div operator, if any operand returns a long item, the item returned by the other operand is cast to a long value (if not a long already) and the result is a long item that is computed using java's arithmetic on longs.
Except for the div operator, if all operands return integer items, the result is an integer item that is computed using java's arithmetic on ints.
The div operator performs floating-point division, even if both its operands are longs and/or integers. In this case, div returns a double.

Oracle NoSQL Database supports the unary + and – operators as well. The unary + is a no-op, and the unary – changes the sign of its numeric argument.

To follow along with the examples, create the user data and airline baggage tracking application table, and insert the data as described in the Tables Used in the Examples section.

Example 6-86 Arithmetic Expression

For each user show their id and the difference between their actual income and an income that is computed as a base income plus an age-proportional amount.

DECLARE
$baseIncome INTEGER;
$ageMultiplier DOUBLE;
SELECT id,
income - ($baseIncome + age * $ageMultiplier) AS adjustment
FROM Users

Example 6-87 Fetch the name, number of transits, and calculate the transit time for the passenger with ticket number 1762320369957 from the airline baggage tracking application data

SELECT bag.fullname AS NAME, $t-1 AS HOPS,
  CASE
    WHEN $t-1 = 2
    THEN get_duration(timestamp_diff($bagInfo.flightLegs[2].flightDate, $bagInfo.flightLegs[1].estimatedArrival) + timestamp_diff($bagInfo.flightLegs[1].flightDate, $bagInfo.flightLegs[0].estimatedArrival))
    WHEN $t-1 = 1        
    THEN get_duration(timestamp_diff($bagInfo.flightLegs[1].flightDate, $bagInfo.flightLegs[0].estimatedArrival))
    ELSE "Direct flight"
  END AS TRANSITHRS
FROM BaggageInfo bag, bag.bagInfo[0] AS $bagInfo,
size($bagInfo.flightLegs) $t
WHERE bag.ticketNo = 1762320369957

Explanation: In the BaggageInfo table, the JSON field bagInfo stores the checked baggage tracking information for each passenger. Passengers can have multiple transits in their air travel. The flightLegs array contains the information on each airport in the passenger's travel itinerary. In each element of the flightLegs array, the flightDate field holds the scheduled departure time from the source airport and the estimatedArrival field holds the estimated arrival time at the destination airport. You use the size function to calculate the size of the flightLegs array, which is the number of travel legs in the passenger's air travel. However, this count includes even the last airport, which is the destination and not the transit airport. To calculate the number of transit airports (hops) per passenger, you use the arithmetic operator '-' to subtract the last airport from the count. Notice that you can assign the size calculation to an internal variable $t1 to facilitate the usage throughout the query. For more details on variables, see Variable Declaration.

To calculate the time spent by a passenger in each transit, you find the duration between the flightDate of the next immediate destination airport and the estimatedArrival of the current airport. You use the timestamp_diff function to calculate the time difference. Repeat this for all the transits until the flightDate field belongs to the final destination airport. Use the arithmetic operator '+' to add the individual transit times to arrive at the total transit time. Use the get_duration function to convert the result from milliseconds to a duration string. Since the number of hops is not fixed for all the passengers, you use the CASE expression with a condition each for the number of hops. Depending on the number of hops for a given passenger, the corresponding condition applies, and the total transit time is calculated and displayed in the result. Here, only two hops are considered for ease of calculation.

The passengers may have more than one checked bag, in which case there will be more than one element in the bagInfo array. You must consider only the first element of the bagInfo array, that is, bagInfo[0] during calculation to avoid duplication of results.

Output:

{"NAME":"Lorenzo Phil","TRANSITHRS":"6 hours 8 minutes"}