Categories:

Aggregate Functions (Boolean) , Window Functions , Conditional Expression Functions

BOOLAND_AGG

Returns the logical (boolean) AND value of all non-NULL boolean records in a group.

BOOLAND_AGG returns TRUE if and only if all records in the group evaluate to TRUE.

If all records inside 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> )

Window function

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

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

The data type of the returned value is BOOLEAN.

Usage Notes

  • Numeric values, Decimal, and floating point values are converted to TRUE if they are different from zero.

  • Character/text types are not supported as they cannot be converted to Boolean.

  • When used as a window function:

    • This function does not support:

      • ORDER BY sub-clause in the OVER clause.

      • 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);

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 |
+----+------+-------+-------+-------+

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           |
+-----------------+-----------------+-----------------+-----------------+

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);

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 |
+----+-------+-------+-------+-------+

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                                        |
+----+----------------------------------------------+----------------------------------------------+----------------------------------------------+----------------------------------------------+

Error example

If this function is passed strings that cannot be converted to Boolean, the function will give an error:

select booland_agg('invalid type');

100037 (22018): Boolean value 'invalid_type' is not recognized