Previous Next JavaScript must be enabled to correctly display this content

## 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"}