Variable References

Syntax

variable_reference ::= "$" [id]

Semantics

A variable-reference expression returns the item that the specified variable is currently bound to. Syntactically, a variable-reference expression is just the name of the variable.

Oracle NoSQL Database allows the declaration of both external and internal variables. For more details on declaring the variables and their scope, see Variable Declaration.

The table aliases are essentially internal variables ranging over the rows of the specified table. The other clauses of the SELECT expression operate on the rows produced by the FROM clause, processing one row at a time. The row currently being processed is called the context row. The columns of the context row can be referenced in expressions either directly by their names or by the table alias followed by a dot character and the column name. If the table alias starts with a dollar sign ($), then it serves as an internal variable declaration for a variable whose name is the alias. This internal variable is bound to the context row as a whole and can be referenced within sub-expressions of the SELECT expression. It can also be passed as an argument to a function to fetch the result for the context row.

Example 6-92 Fetch the duration between the baggage boarding and baggage collection from the destination airport for a passenger from the airline baggage tracking application data

SELECT $flightLeg.flightDate AS BAGGAGE_CHECK, $bagInfo.bagArrivalDate AS BAGGAGE_COLLECT, 
$t1 AS BAGGAGE_DURATION 
FROM BaggageInfo bag, bag.bagInfo[0] AS $bagInfo, $bagInfo.flightLegs[0] AS $flightLeg,
get_duration(timestamp_diff($bagInfo.bagArrivalDate, $flightLeg.flightDate)) $t1
WHERE ticketNo=1762320369957

Explanation: In this example, you use table alias and internal variables. You can determine the duration between the checked time and the baggage collection time at the destination airport by calculating the time difference between bagArrivalDate and flightDate[0] for each passenger. You use the timestamp_diff function to find the time difference and the get_duration function to convert the result from milliseconds to a duration string. For more details, see timestamp_diff and get_duration functions.

The table name BaggageInfo in the query is followed by a table alias bag. You use the variables $bagInfo and $flightLeg to reference the columns in the context row (the row that is currently being processed). Notice that bagInfo is a column of type array in the BaggageInfo table, whereas $bagInfo is an internal variable used to hold the first element of the bagInfo array for the context row. $t1 is an internal variable whose value is assigned when the query is executed.

Note:

Here, the timestamp_diff function automatically casts the bagArrivalDate and flightDate from String to a timestamp data type during the query run time.

Output:

{"BAGGAGE_CHECK":"2019-03-12T03:00:00Z","BAGGAGE_COLLECT":"2019-03-12T15:05:00Z","BAGGAGE_DURATION":"12
      hours 5 minutes"}