Expressions and operators

PRQL

SQL

WITH table_1 AS (
  SELECT
    diameter * 3.14159 AS circumference,
    color
  FROM
    foo
)
SELECT
  color
FROM
  table_1 AS table_0
WHERE
  circumference > 10
  AND color <> 'red'

This table shows operator precedence. Use parentheses () to prioritize operations and for function calls (see the discussion below.)

PRQL uses parentheses () for several purposes:

• Parentheses group operands to control the order of evaluation, for example: ((1 + x) * y)

• Parentheses delimit a minus sign of a function argument, for example: add (-1) (-3)

• Parentheses delimit nested function calls that contain a pipe, either the | symbol or a new line. “Nested” means within a transform; i.e. not just the main pipeline, for example: (column-name | in 0..20)

• Parentheses wrap a function call that is part of a larger expression on the right-hand side of an assignment, for example: round 0 (sum distance)

Here’s a set of examples of these rules:

SELECT
  distance BETWEEN 0 AND 20 AS is_proximate,
  SUM(distance) OVER () AS total_distance,
  distance / 40 AS travel_time,
  ROUND(distance, 2) AS distance_rounded_2_dp,
  distance >= 100 AS is_far,
  distance BETWEEN -100 AND 0,
  distance BETWEEN -100 AND 0 AS is_negative,
  AVG(distance) OVER () AS average_distance
FROM
  employees
ORDER BY
  distance DESC

Note: The total_distance statement below generates an error because the function is not in a list. (The PRQL compiler should display a better error message.)

Error:
   ╭─[:2:29]
   │
 2 │ derive total_distance = sum distance # generates the error shown below
   │                             ────┬───

Note