The jOOQ User Manual : SQL building : Column expressions : Aggregate functions : BIT_OR_AGG | previous : next |
This documentation is for the unreleased development version of jOOQ. Click on the above version links to get this documentation for a supported version of jOOQ.
BIT_OR_AGG
Applies to ✅ Open Source Edition ✅ Express Edition ✅ Professional Edition ✅ Enterprise Edition
An aggregate function to perform the equivalent of the BIT_OR function on a data set. In other words, the resulting bits are:
-
1
at positionp
if the argument is1
at positionp
for at least one row in the group. -
0
at positionp
if the argument is0
at positionp
for every row in the group.
As with most aggregate functions, NULL
values are not aggregated.
SELECT bit_or_agg(ID), bit_or_agg(AUTHOR_ID) FROM BOOK
create.select( bitOrAgg(BOOK.ID), bitOrAgg(BOOK.AUTHOR_ID)) .from(BOOK)
Producing:
+------------+------------+ | bit_or_agg | bit_or_agg | +------------+------------+ | 7 | 3 | +------------+------------+
Dialect support
This example using jOOQ:
bitOrAgg(BOOK.ID.coerce(TINYINT))
Translates to the following dialect specific expressions:
-- ASE, SQLDATAWAREHOUSE, SQLSERVER (CASE WHEN CASE WHEN max(CASE WHEN (BOOK.ID & 1) <> 0 THEN 1 ELSE 0 END) = 1 THEN 1 WHEN NOT (max(CASE WHEN (BOOK.ID & 1) <> 0 THEN 1 ELSE 0 END) = 1) THEN 0 END = 1 THEN 1 ELSE 0 END + CASE WHEN CASE WHEN max(CASE WHEN (BOOK.ID & 2) <> 0 THEN 1 ELSE 0 END) = 1 THEN 1 WHEN NOT (max(CASE WHEN (BOOK.ID & 2) <> 0 THEN 1 ELSE 0 END) = 1) THEN 0 END = 1 THEN 2 ELSE 0 END + CASE WHEN CASE WHEN max(CASE WHEN (BOOK.ID & 4) <> 0 THEN 1 ELSE 0 END) = 1 THEN 1 WHEN NOT (max(CASE WHEN (BOOK.ID & 4) <> 0 THEN 1 ELSE 0 END) = 1) THEN 0 END = 1 THEN 4 ELSE 0 END + CASE WHEN CASE WHEN max(CASE WHEN (BOOK.ID & 8) <> 0 THEN 1 ELSE 0 END) = 1 THEN 1 WHEN NOT (max(CASE WHEN (BOOK.ID & 8) <> 0 THEN 1 ELSE 0 END) = 1) THEN 0 END = 1 THEN 8 ELSE 0 END + CASE WHEN CASE WHEN max(CASE WHEN (BOOK.ID & 16) <> 0 THEN 1 ELSE 0 END) = 1 THEN 1 WHEN NOT (max(CASE WHEN (BOOK.ID & 16) <> 0 THEN 1 ELSE 0 END) = 1) THEN 0 END = 1 THEN 16 ELSE 0 END + CASE WHEN CASE WHEN max(CASE WHEN (BOOK.ID & 32) <> 0 THEN 1 ELSE 0 END) = 1 THEN 1 WHEN NOT (max(CASE WHEN (BOOK.ID & 32) <> 0 THEN 1 ELSE 0 END) = 1) THEN 0 END = 1 THEN 32 ELSE 0 END + CASE WHEN CASE WHEN max(CASE WHEN (BOOK.ID & 64) <> 0 THEN 1 ELSE 0 END) = 1 THEN 1 WHEN NOT (max(CASE WHEN (BOOK.ID & 64) <> 0 THEN 1 ELSE 0 END) = 1) THEN 0 END = 1 THEN 64 ELSE 0 END + CASE WHEN CASE WHEN max(CASE WHEN (BOOK.ID & -128) <> 0 THEN 1 ELSE 0 END) = 1 THEN 1 WHEN NOT (max(CASE WHEN (BOOK.ID & -128) <> 0 THEN 1 ELSE 0 END) = 1) THEN 0 END = 1 THEN -128 ELSE 0 END) -- AURORA_MYSQL, AURORA_POSTGRES, H2, ORACLE, SNOWFLAKE bit_or_agg(BOOK.ID) -- BIGQUERY, COCKROACHDB, MARIADB, MYSQL, POSTGRES, SYBASE, VERTICA, YUGABYTEDB bit_or(BOOK.ID) -- DB2, TERADATA (CASE WHEN CASE WHEN max(CASE WHEN bitand( BOOK.ID, 1 ) <> 0 THEN 1 ELSE 0 END) = 1 THEN 1 WHEN NOT (max(CASE WHEN bitand( BOOK.ID, 1 ) <> 0 THEN 1 ELSE 0 END) = 1) THEN 0 END = 1 THEN 1 ELSE 0 END + CASE WHEN CASE WHEN max(CASE WHEN bitand( BOOK.ID, 2 ) <> 0 THEN 1 ELSE 0 END) = 1 THEN 1 WHEN NOT (max(CASE WHEN bitand( BOOK.ID, 2 ) <> 0 THEN 1 ELSE 0 END) = 1) THEN 0 END = 1 THEN 2 ELSE 0 END + CASE WHEN CASE WHEN max(CASE WHEN bitand( BOOK.ID, 4 ) <> 0 THEN 1 ELSE 0 END) = 1 THEN 1 WHEN NOT (max(CASE WHEN bitand( BOOK.ID, 4 ) <> 0 THEN 1 ELSE 0 END) = 1) THEN 0 END = 1 THEN 4 ELSE 0 END + CASE WHEN CASE WHEN max(CASE WHEN bitand( BOOK.ID, 8 ) <> 0 THEN 1 ELSE 0 END) = 1 THEN 1 WHEN NOT (max(CASE WHEN bitand( BOOK.ID, 8 ) <> 0 THEN 1 ELSE 0 END) = 1) THEN 0 END = 1 THEN 8 ELSE 0 END + CASE WHEN CASE WHEN max(CASE WHEN bitand( BOOK.ID, 16 ) <> 0 THEN 1 ELSE 0 END) = 1 THEN 1 WHEN NOT (max(CASE WHEN bitand( BOOK.ID, 16 ) <> 0 THEN 1 ELSE 0 END) = 1) THEN 0 END = 1 THEN 16 ELSE 0 END + CASE WHEN CASE WHEN max(CASE WHEN bitand( BOOK.ID, 32 ) <> 0 THEN 1 ELSE 0 END) = 1 THEN 1 WHEN NOT (max(CASE WHEN bitand( BOOK.ID, 32 ) <> 0 THEN 1 ELSE 0 END) = 1) THEN 0 END = 1 THEN 32 ELSE 0 END + CASE WHEN CASE WHEN max(CASE WHEN bitand( BOOK.ID, 64 ) <> 0 THEN 1 ELSE 0 END) = 1 THEN 1 WHEN NOT (max(CASE WHEN bitand( BOOK.ID, 64 ) <> 0 THEN 1 ELSE 0 END) = 1) THEN 0 END = 1 THEN 64 ELSE 0 END + CASE WHEN CASE WHEN max(CASE WHEN bitand( BOOK.ID, -128 ) <> 0 THEN 1 ELSE 0 END) = 1 THEN 1 WHEN NOT (max(CASE WHEN bitand( BOOK.ID, -128 ) <> 0 THEN 1 ELSE 0 END) = 1) THEN 0 END = 1 THEN -128 ELSE 0 END) -- FIREBIRD (CASE WHEN CASE WHEN max(CASE WHEN bin_and( BOOK.ID, 1 ) <> 0 THEN 1 ELSE 0 END) = 1 THEN 1 WHEN NOT (max(CASE WHEN bin_and( BOOK.ID, 1 ) <> 0 THEN 1 ELSE 0 END) = 1) THEN 0 END = 1 THEN 1 ELSE 0 END + CASE WHEN CASE WHEN max(CASE WHEN bin_and( BOOK.ID, 2 ) <> 0 THEN 1 ELSE 0 END) = 1 THEN 1 WHEN NOT (max(CASE WHEN bin_and( BOOK.ID, 2 ) <> 0 THEN 1 ELSE 0 END) = 1) THEN 0 END = 1 THEN 2 ELSE 0 END + CASE WHEN CASE WHEN max(CASE WHEN bin_and( BOOK.ID, 4 ) <> 0 THEN 1 ELSE 0 END) = 1 THEN 1 WHEN NOT (max(CASE WHEN bin_and( BOOK.ID, 4 ) <> 0 THEN 1 ELSE 0 END) = 1) THEN 0 END = 1 THEN 4 ELSE 0 END + CASE WHEN CASE WHEN max(CASE WHEN bin_and( BOOK.ID, 8 ) <> 0 THEN 1 ELSE 0 END) = 1 THEN 1 WHEN NOT (max(CASE WHEN bin_and( BOOK.ID, 8 ) <> 0 THEN 1 ELSE 0 END) = 1) THEN 0 END = 1 THEN 8 ELSE 0 END + CASE WHEN CASE WHEN max(CASE WHEN bin_and( BOOK.ID, 16 ) <> 0 THEN 1 ELSE 0 END) = 1 THEN 1 WHEN NOT (max(CASE WHEN bin_and( BOOK.ID, 16 ) <> 0 THEN 1 ELSE 0 END) = 1) THEN 0 END = 1 THEN 16 ELSE 0 END + CASE WHEN CASE WHEN max(CASE WHEN bin_and( BOOK.ID, 32 ) <> 0 THEN 1 ELSE 0 END) = 1 THEN 1 WHEN NOT (max(CASE WHEN bin_and( BOOK.ID, 32 ) <> 0 THEN 1 ELSE 0 END) = 1) THEN 0 END = 1 THEN 32 ELSE 0 END + CASE WHEN CASE WHEN max(CASE WHEN bin_and( BOOK.ID, 64 ) <> 0 THEN 1 ELSE 0 END) = 1 THEN 1 WHEN NOT (max(CASE WHEN bin_and( BOOK.ID, 64 ) <> 0 THEN 1 ELSE 0 END) = 1) THEN 0 END = 1 THEN 64 ELSE 0 END + CASE WHEN CASE WHEN max(CASE WHEN bin_and( BOOK.ID, -128 ) <> 0 THEN 1 ELSE 0 END) = 1 THEN 1 WHEN NOT (max(CASE WHEN bin_and( BOOK.ID, -128 ) <> 0 THEN 1 ELSE 0 END) = 1) THEN 0 END = 1 THEN -128 ELSE 0 END) -- HANA (CASE WHEN CASE WHEN max(CASE WHEN bitand( BOOK.ID, 1 ) <> 0 THEN 1 ELSE 0 END) = 1 THEN TRUE WHEN NOT (max(CASE WHEN bitand( BOOK.ID, 1 ) <> 0 THEN 1 ELSE 0 END) = 1) THEN FALSE END = TRUE THEN 1 ELSE 0 END + CASE WHEN CASE WHEN max(CASE WHEN bitand( BOOK.ID, 2 ) <> 0 THEN 1 ELSE 0 END) = 1 THEN TRUE WHEN NOT (max(CASE WHEN bitand( BOOK.ID, 2 ) <> 0 THEN 1 ELSE 0 END) = 1) THEN FALSE END = TRUE THEN 2 ELSE 0 END + CASE WHEN CASE WHEN max(CASE WHEN bitand( BOOK.ID, 4 ) <> 0 THEN 1 ELSE 0 END) = 1 THEN TRUE WHEN NOT (max(CASE WHEN bitand( BOOK.ID, 4 ) <> 0 THEN 1 ELSE 0 END) = 1) THEN FALSE END = TRUE THEN 4 ELSE 0 END + CASE WHEN CASE WHEN max(CASE WHEN bitand( BOOK.ID, 8 ) <> 0 THEN 1 ELSE 0 END) = 1 THEN TRUE WHEN NOT (max(CASE WHEN bitand( BOOK.ID, 8 ) <> 0 THEN 1 ELSE 0 END) = 1) THEN FALSE END = TRUE THEN 8 ELSE 0 END + CASE WHEN CASE WHEN max(CASE WHEN bitand( BOOK.ID, 16 ) <> 0 THEN 1 ELSE 0 END) = 1 THEN TRUE WHEN NOT (max(CASE WHEN bitand( BOOK.ID, 16 ) <> 0 THEN 1 ELSE 0 END) = 1) THEN FALSE END = TRUE THEN 16 ELSE 0 END + CASE WHEN CASE WHEN max(CASE WHEN bitand( BOOK.ID, 32 ) <> 0 THEN 1 ELSE 0 END) = 1 THEN TRUE WHEN NOT (max(CASE WHEN bitand( BOOK.ID, 32 ) <> 0 THEN 1 ELSE 0 END) = 1) THEN FALSE END = TRUE THEN 32 ELSE 0 END + CASE WHEN CASE WHEN max(CASE WHEN bitand( BOOK.ID, 64 ) <> 0 THEN 1 ELSE 0 END) = 1 THEN TRUE WHEN NOT (max(CASE WHEN bitand( BOOK.ID, 64 ) <> 0 THEN 1 ELSE 0 END) = 1) THEN FALSE END = TRUE THEN 64 ELSE 0 END + CASE WHEN CASE WHEN max(CASE WHEN bitand( BOOK.ID, -128 ) <> 0 THEN 1 ELSE 0 END) = 1 THEN TRUE WHEN NOT (max(CASE WHEN bitand( BOOK.ID, -128 ) <> 0 THEN 1 ELSE 0 END) = 1) THEN FALSE END = TRUE THEN -128 ELSE 0 END) -- HSQLDB, INFORMIX (CASE WHEN (max(CASE WHEN bitand( BOOK.ID, 1 ) <> 0 THEN 1 ELSE 0 END) = 1) THEN 1 ELSE 0 END + CASE WHEN (max(CASE WHEN bitand( BOOK.ID, 2 ) <> 0 THEN 1 ELSE 0 END) = 1) THEN 2 ELSE 0 END + CASE WHEN (max(CASE WHEN bitand( BOOK.ID, 4 ) <> 0 THEN 1 ELSE 0 END) = 1) THEN 4 ELSE 0 END + CASE WHEN (max(CASE WHEN bitand( BOOK.ID, 8 ) <> 0 THEN 1 ELSE 0 END) = 1) THEN 8 ELSE 0 END + CASE WHEN (max(CASE WHEN bitand( BOOK.ID, 16 ) <> 0 THEN 1 ELSE 0 END) = 1) THEN 16 ELSE 0 END + CASE WHEN (max(CASE WHEN bitand( BOOK.ID, 32 ) <> 0 THEN 1 ELSE 0 END) = 1) THEN 32 ELSE 0 END + CASE WHEN (max(CASE WHEN bitand( BOOK.ID, 64 ) <> 0 THEN 1 ELSE 0 END) = 1) THEN 64 ELSE 0 END + CASE WHEN (max(CASE WHEN bitand( BOOK.ID, -128 ) <> 0 THEN 1 ELSE 0 END) = 1) THEN -128 ELSE 0 END) -- MEMSQL, REDSHIFT, SQLITE (CASE WHEN (max(CASE WHEN (BOOK.ID & 1) <> 0 THEN 1 ELSE 0 END) = 1) THEN 1 ELSE 0 END + CASE WHEN (max(CASE WHEN (BOOK.ID & 2) <> 0 THEN 1 ELSE 0 END) = 1) THEN 2 ELSE 0 END + CASE WHEN (max(CASE WHEN (BOOK.ID & 4) <> 0 THEN 1 ELSE 0 END) = 1) THEN 4 ELSE 0 END + CASE WHEN (max(CASE WHEN (BOOK.ID & 8) <> 0 THEN 1 ELSE 0 END) = 1) THEN 8 ELSE 0 END + CASE WHEN (max(CASE WHEN (BOOK.ID & 16) <> 0 THEN 1 ELSE 0 END) = 1) THEN 16 ELSE 0 END + CASE WHEN (max(CASE WHEN (BOOK.ID & 32) <> 0 THEN 1 ELSE 0 END) = 1) THEN 32 ELSE 0 END + CASE WHEN (max(CASE WHEN (BOOK.ID & 64) <> 0 THEN 1 ELSE 0 END) = 1) THEN 64 ELSE 0 END + CASE WHEN (max(CASE WHEN (BOOK.ID & -128) <> 0 THEN 1 ELSE 0 END) = 1) THEN -128 ELSE 0 END) -- ACCESS, DERBY, EXASOL /* UNSUPPORTED */
(These are currently generated with jOOQ 3.18, see #10141), or translate your own on our website
Feedback
Do you have any feedback about this page? We'd love to hear it!