Categories:

Aggregate functions (Boolean) , Window functions , Conditional expression functions

BOOLAND_AGG

Returns TRUE if all non-NULL Boolean records in a group evaluate to TRUE.

If all records in the group are NULL, or if the group is empty, the function returns NULL.

See also:

BOOLAND , BOOLOR_AGG , BOOLXOR_AGG

Syntax

Aggregate function

BOOLAND_AGG( <expr> )
Copy

Window function

BOOLAND_AGG( <expr> ) OVER ( [ PARTITION BY <partition_expr> ] )
Copy

Arguments

expr

The input expression must be an expression that can be evaluated to a boolean or converted to a boolean.

partition_expr

This column or expression specifies how to separate the input into partitions (sub-windows).

Returns

This function returns a value of type BOOLEAN.

Usage notes

  • Numeric values are converted to TRUE if they are non-zero.

  • String and binary values aren’t supported because they can’t be converted to Boolean values.

  • When this function is called as a window function, it does not support:

    • An ORDER BY clause within the OVER clause.

    • Explicit window frames.

Examples

Aggregate function

The following example shows that booland_agg returns true when all of the input values are true.

Create and load the table:

create or replace table test_boolean_agg( id integer, c1 boolean, c2 boolean, c3 boolean, c4 boolean ); insert into test_boolean_agg (id, c1, c2, c3, c4) values (1, true, true, true, false), (2, true, false, false, false), (3, true, true, false, false), (4, true, false, false, false);
Copy

Display the data:

select * from test_boolean_agg; +----+------+-------+-------+-------+ | ID | C1 | C2 | C3 | C4 | |----+------+-------+-------+-------| | 1 | True | True | True | False | | 2 | True | False | False | False | | 3 | True | True | False | False | | 4 | True | False | False | False | +----+------+-------+-------+-------+
Copy

Query the data:

select booland_agg(c1), booland_agg(c2), booland_agg(c3), booland_agg(c4) from test_boolean_agg; +-----------------+-----------------+-----------------+-----------------+ | BOOLAND_AGG(C1) | BOOLAND_AGG(C2) | BOOLAND_AGG(C3) | BOOLAND_AGG(C4) | |-----------------+-----------------+-----------------+-----------------| | True | False | False | False | +-----------------+-----------------+-----------------+-----------------+
Copy

Window function

This example is similar to the previous example, but it shows usage as a window function, with the input rows split into two partitions (one for IDs greater than 0 and one for IDs less than or equal to 0). Additional data was added to the table.

Add rows to the table:

insert into test_boolean_agg (id, c1, c2, c3, c4) values (-4, false, false, false, true), (-3, false, true, true, true), (-2, false, false, true, true), (-1, false, true, true, true);
Copy

Display the data:

select * from test_boolean_agg order by id; +----+-------+-------+-------+-------+ | ID | C1 | C2 | C3 | C4 | |----+-------+-------+-------+-------| | -4 | False | False | False | True | | -3 | False | True | True | True | | -2 | False | False | True | True | | -1 | False | True | True | True | | 1 | True | True | True | False | | 2 | True | False | False | False | | 3 | True | True | False | False | | 4 | True | False | False | False | +----+-------+-------+-------+-------+
Copy

Query the data:

select id, booland_agg(c1) OVER (PARTITION BY (id > 0)), booland_agg(c2) OVER (PARTITION BY (id > 0)), booland_agg(c3) OVER (PARTITION BY (id > 0)), booland_agg(c4) OVER (PARTITION BY (id > 0)) from test_boolean_agg order by id; +----+----------------------------------------------+----------------------------------------------+----------------------------------------------+----------------------------------------------+ | ID | BOOLAND_AGG(C1) OVER (PARTITION BY (ID > 0)) | BOOLAND_AGG(C2) OVER (PARTITION BY (ID > 0)) | BOOLAND_AGG(C3) OVER (PARTITION BY (ID > 0)) | BOOLAND_AGG(C4) OVER (PARTITION BY (ID > 0)) | |----+----------------------------------------------+----------------------------------------------+----------------------------------------------+----------------------------------------------| | -4 | False | False | False | True | | -3 | False | False | False | True | | -2 | False | False | False | True | | -1 | False | False | False | True | | 1 | True | False | False | False | | 2 | True | False | False | False | | 3 | True | False | False | False | | 4 | True | False | False | False | +----+----------------------------------------------+----------------------------------------------+----------------------------------------------+----------------------------------------------+
Copy

Error example

If this function is passed strings that can’t be converted to Boolean, the function returns an error:

select booland_agg('invalid type'); 100037 (22018): Boolean value 'invalid_type' is not recognized
Copy
Language: English