Menu
Amazon Redshift
Database Developer Guide (API Version 2012-12-01)

Compound Expressions

A compound expression is a series of simple expressions joined by arithmetic operators. A simple expression used in a compound expression must return a numeric value.

Syntax

Copy
expression operator expression | (compound_expression)

Arguments

expression

A simple expression that evaluates to a value.

operator

A compound arithmetic expression can be constructed using the following operators, in this order of precedence:

  • ( ) : parentheses to control the order of evaluation

  • + , - : positive and negative sign/operator

  • ^ , |/ , ||/ : exponentiation, square root, cube root

  • * , / , % : multiplication, division, and modulo operators

  • @ : absolute value

  • + , - : addition and subtraction

  • & , |, #, ~, <<, >> : AND, OR, XOR, NOT, shift left, shift right bitwise operators

  • ||: concatenation

(compound_expression)

Compound expressions may be nested using parentheses.

Examples

Examples of compound expressions include:

Copy
('SMITH' || 'JONES') sum(x) / y sqrt(256) * avg(column) rank() over (order by qtysold) / 100 (select (pricepaid - commission) from sales where dateid = 1882) * (qtysold)

Some functions can also be nested within other functions. For example, any scalar function can nest within another scalar function. The following example returns the sum of the absolute values of a set of numbers:

Copy
sum(abs(qtysold))

Window functions cannot be used as arguments for aggregate functions or other window functions. The following expression would return an error:

Copy
avg(rank() over (order by qtysold))

Window functions can have a nested aggregate function. The following expression sums sets of values and then ranks them:

Copy
rank() over (order by sum(qtysold))

On this page: