Creating a variable and filtering the output - Cloud - 8.0

Talend Data Shaping Language Reference Guide
Version
Cloud
8.0
Language
English
Product
Talend Cloud
Module
Data Shaping Language
Content
Design and Development
Last publication date
2023-11-23
Create a variable to calculate the total price for each item and use it to filter your output.

Procedure

  1. Under the UNNEST clause in the query, use the LET keyword to create a variable and assign to the variable an expression that calculates the total price for an item.
    You can do this by multiplying the price element by the qty element: LET $total = toDecimal(qty * price)
  2. Add a total_price element with the value of the $total variable in the SELECT clause.
    The SELECT clause should look like this:
    SELECT {
    c.name,
    c.rating,
    o.orderno,
    itemno,
    total_price = $total
}
  3. Under the LET clause, add a WHERE clause to keep the items with a total price above 100: WHERE $total > 1000.
  4. Under the JOIN clause, add another WHERE clause to keep the customers with a rating above 650: WHERE c.rating > 650.
  5. To return an output with only the item with the highest total price, add the following clause after the SELECT clause: ORDER BY $total DESC LIMIT 1.
    This orders the output from highest to lowest total, and keeps only the first result.

Results

The full query should look like this:
FROM order AS o
UNNEST o.items
LET $total = toDecimal(qty * price)
WHERE $total > 1000
JOIN customer AS c ON c.custid = o.custid
WHERE c.rating > 650
SELECT {
    c.name,
    c.rating,
    o.orderno,
    itemno,
    total_price = $total
} ORDER BY $total DESC LIMIT 1
It return the following result:
[
	{
		"name": "T. Cruise",
		"rating": 750,
		"orderno": 1002,
		"itemno": 460,
		"total_price": 9594.05
	}
]