- 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:
Syntax¶
Aggregate function
BOOLOR_AGG( <expr> )
Window function
BOOLOR_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 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);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 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 | +----------------+----------------+----------------+----------------+
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, 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 | +----+---------------------------------------------+---------------------------------------------+---------------------------------------------+---------------------------------------------+
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