Categories:

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

BOOLOR_AGG

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

BOOLOR_AGG returns ‘true’ if at least one record in the group evaluates to ‘true’.

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

See also:

BOOLOR , BOOLAND_AGG , BOOLXOR_AGG

Syntax

Aggregate function

BOOLOR_AGG( <expr> )
Copy

Window function

BOOLOR_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

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 boolor_agg returns true if at least one of the input values is 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 boolor_agg(c1), boolor_agg(c2), boolor_agg(c3), boolor_agg(c4)
    from test_boolean_agg;
+----------------+----------------+----------------+----------------+
| BOOLOR_AGG(C1) | BOOLOR_AGG(C2) | BOOLOR_AGG(C3) | BOOLOR_AGG(C4) |
|----------------+----------------+----------------+----------------|
| True           | True           | True           | 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,
      boolor_agg(c1) OVER (PARTITION BY (id > 0)),
      boolor_agg(c2) OVER (PARTITION BY (id > 0)),
      boolor_agg(c3) OVER (PARTITION BY (id > 0)),
      boolor_agg(c4) OVER (PARTITION BY (id > 0))
    from test_boolean_agg
    order by id;
+----+---------------------------------------------+---------------------------------------------+---------------------------------------------+---------------------------------------------+
| ID | BOOLOR_AGG(C1) OVER (PARTITION BY (ID > 0)) | BOOLOR_AGG(C2) OVER (PARTITION BY (ID > 0)) | BOOLOR_AGG(C3) OVER (PARTITION BY (ID > 0)) | BOOLOR_AGG(C4) OVER (PARTITION BY (ID > 0)) |
|----+---------------------------------------------+---------------------------------------------+---------------------------------------------+---------------------------------------------|
| -4 | False                                       | True                                        | True                                        | True                                        |
| -3 | False                                       | True                                        | True                                        | True                                        |
| -2 | False                                       | True                                        | True                                        | True                                        |
| -1 | False                                       | True                                        | True                                        | True                                        |
|  1 | True                                        | True                                        | True                                        | False                                       |
|  2 | True                                        | True                                        | True                                        | False                                       |
|  3 | True                                        | True                                        | True                                        | False                                       |
|  4 | True                                        | True                                        | True                                        | False                                       |
+----+---------------------------------------------+---------------------------------------------+---------------------------------------------+---------------------------------------------+
Copy

Error example

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

select boolor_agg('invalid type');

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