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_NOR_AGG
Applies to ✅ Open Source Edition ✅ Express Edition ✅ Professional Edition ✅ Enterprise Edition
An aggregate function to perform the equivalent of the BIT_NOR function on a data set. In other words, the resulting bits are:
-
0
at positionp
if the argument is1
at positionp
for at least one row in the group. -
1
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_nor_agg(ID), bit_nor_agg(AUTHOR_ID) FROM BOOK
create.select( bitNorAgg(BOOK.ID), bitNorAgg(BOOK.AUTHOR_ID)) .from(BOOK)
Producing:
+-------------+--------------+ | bit_nor_agg | bit_nor_agg | +-------------+--------------+ | -8 | -4 | +-------------+--------------+
Dialect support
This example using jOOQ:
bitNorAgg(BOOK.ID.coerce(TINYINT))
Translates to the following dialect specific expressions:
ASE, MemSQL, Redshift, SQLDataWarehouse, SQLServer, SQLite
~((CASE max( CASE (BOOK.ID & 1) WHEN 0 THEN 0 WHEN 1 THEN 1 END ) WHEN 1 THEN 1 WHEN 0 THEN 0 END + CASE max( CASE (BOOK.ID & 2) WHEN 0 THEN 0 WHEN 2 THEN 2 END ) WHEN 2 THEN 2 WHEN 0 THEN 0 END + CASE max( CASE (BOOK.ID & 4) WHEN 0 THEN 0 WHEN 4 THEN 4 END ) WHEN 4 THEN 4 WHEN 0 THEN 0 END + CASE max( CASE (BOOK.ID & 8) WHEN 0 THEN 0 WHEN 8 THEN 8 END ) WHEN 8 THEN 8 WHEN 0 THEN 0 END + CASE max( CASE (BOOK.ID & 16) WHEN 0 THEN 0 WHEN 16 THEN 16 END ) WHEN 16 THEN 16 WHEN 0 THEN 0 END + CASE max( CASE (BOOK.ID & 32) WHEN 0 THEN 0 WHEN 32 THEN 32 END ) WHEN 32 THEN 32 WHEN 0 THEN 0 END + CASE max( CASE (BOOK.ID & 64) WHEN 0 THEN 0 WHEN 64 THEN 64 END ) WHEN 64 THEN 64 WHEN 0 THEN 0 END + CASE max( CASE (BOOK.ID & -128) WHEN 0 THEN 0 WHEN -128 THEN -128 END ) WHEN -128 THEN -128 WHEN 0 THEN 0 END))
Aurora MySQL, Aurora Postgres
~(bit_or_agg(BOOK.ID))
BigQuery, CockroachDB, Databricks, DuckDB, MariaDB, MySQL, Postgres, Sybase, YugabyteDB
~(bit_or(BOOK.ID))
ClickHouse
bitNot(groupBitOr(BOOK.ID))
DB2, Hana, Informix, Teradata
bitnot((CASE max( CASE bitand( BOOK.ID, 1 ) WHEN 0 THEN 0 WHEN 1 THEN 1 END ) WHEN 1 THEN 1 WHEN 0 THEN 0 END + CASE max( CASE bitand( BOOK.ID, 2 ) WHEN 0 THEN 0 WHEN 2 THEN 2 END ) WHEN 2 THEN 2 WHEN 0 THEN 0 END + CASE max( CASE bitand( BOOK.ID, 4 ) WHEN 0 THEN 0 WHEN 4 THEN 4 END ) WHEN 4 THEN 4 WHEN 0 THEN 0 END + CASE max( CASE bitand( BOOK.ID, 8 ) WHEN 0 THEN 0 WHEN 8 THEN 8 END ) WHEN 8 THEN 8 WHEN 0 THEN 0 END + CASE max( CASE bitand( BOOK.ID, 16 ) WHEN 0 THEN 0 WHEN 16 THEN 16 END ) WHEN 16 THEN 16 WHEN 0 THEN 0 END + CASE max( CASE bitand( BOOK.ID, 32 ) WHEN 0 THEN 0 WHEN 32 THEN 32 END ) WHEN 32 THEN 32 WHEN 0 THEN 0 END + CASE max( CASE bitand( BOOK.ID, 64 ) WHEN 0 THEN 0 WHEN 64 THEN 64 END ) WHEN 64 THEN 64 WHEN 0 THEN 0 END + CASE max( CASE bitand( BOOK.ID, -128 ) WHEN 0 THEN 0 WHEN -128 THEN -128 END ) WHEN -128 THEN -128 WHEN 0 THEN 0 END))
Exasol
bit_not((CASE max( CASE bit_and( BOOK.ID, 1 ) WHEN 0 THEN 0 WHEN 1 THEN 1 END ) WHEN 1 THEN 1 WHEN 0 THEN 0 END + CASE max( CASE bit_and( BOOK.ID, 2 ) WHEN 0 THEN 0 WHEN 2 THEN 2 END ) WHEN 2 THEN 2 WHEN 0 THEN 0 END + CASE max( CASE bit_and( BOOK.ID, 4 ) WHEN 0 THEN 0 WHEN 4 THEN 4 END ) WHEN 4 THEN 4 WHEN 0 THEN 0 END + CASE max( CASE bit_and( BOOK.ID, 8 ) WHEN 0 THEN 0 WHEN 8 THEN 8 END ) WHEN 8 THEN 8 WHEN 0 THEN 0 END + CASE max( CASE bit_and( BOOK.ID, 16 ) WHEN 0 THEN 0 WHEN 16 THEN 16 END ) WHEN 16 THEN 16 WHEN 0 THEN 0 END + CASE max( CASE bit_and( BOOK.ID, 32 ) WHEN 0 THEN 0 WHEN 32 THEN 32 END ) WHEN 32 THEN 32 WHEN 0 THEN 0 END + CASE max( CASE bit_and( BOOK.ID, 64 ) WHEN 0 THEN 0 WHEN 64 THEN 64 END ) WHEN 64 THEN 64 WHEN 0 THEN 0 END + CASE max( CASE bit_and( BOOK.ID, -128 ) WHEN 0 THEN 0 WHEN -128 THEN -128 END ) WHEN -128 THEN -128 WHEN 0 THEN 0 END))
Firebird
bin_not((CASE max( CASE bin_and( BOOK.ID, 1 ) WHEN 0 THEN 0 WHEN 1 THEN 1 END ) WHEN 1 THEN 1 WHEN 0 THEN 0 END + CASE max( CASE bin_and( BOOK.ID, 2 ) WHEN 0 THEN 0 WHEN 2 THEN 2 END ) WHEN 2 THEN 2 WHEN 0 THEN 0 END + CASE max( CASE bin_and( BOOK.ID, 4 ) WHEN 0 THEN 0 WHEN 4 THEN 4 END ) WHEN 4 THEN 4 WHEN 0 THEN 0 END + CASE max( CASE bin_and( BOOK.ID, 8 ) WHEN 0 THEN 0 WHEN 8 THEN 8 END ) WHEN 8 THEN 8 WHEN 0 THEN 0 END + CASE max( CASE bin_and( BOOK.ID, 16 ) WHEN 0 THEN 0 WHEN 16 THEN 16 END ) WHEN 16 THEN 16 WHEN 0 THEN 0 END + CASE max( CASE bin_and( BOOK.ID, 32 ) WHEN 0 THEN 0 WHEN 32 THEN 32 END ) WHEN 32 THEN 32 WHEN 0 THEN 0 END + CASE max( CASE bin_and( BOOK.ID, 64 ) WHEN 0 THEN 0 WHEN 64 THEN 64 END ) WHEN 64 THEN 64 WHEN 0 THEN 0 END + CASE max( CASE bin_and( BOOK.ID, -128 ) WHEN 0 THEN 0 WHEN -128 THEN -128 END ) WHEN -128 THEN -128 WHEN 0 THEN 0 END))
H2
bit_nor_agg(BOOK.ID)
HSQLDB
((0 - (CASE max( CASE bitand( BOOK.ID, 1 ) WHEN 0 THEN 0 WHEN 1 THEN 1 END ) WHEN 1 THEN 1 WHEN 0 THEN 0 END + CASE max( CASE bitand( BOOK.ID, 2 ) WHEN 0 THEN 0 WHEN 2 THEN 2 END ) WHEN 2 THEN 2 WHEN 0 THEN 0 END + CASE max( CASE bitand( BOOK.ID, 4 ) WHEN 0 THEN 0 WHEN 4 THEN 4 END ) WHEN 4 THEN 4 WHEN 0 THEN 0 END + CASE max( CASE bitand( BOOK.ID, 8 ) WHEN 0 THEN 0 WHEN 8 THEN 8 END ) WHEN 8 THEN 8 WHEN 0 THEN 0 END + CASE max( CASE bitand( BOOK.ID, 16 ) WHEN 0 THEN 0 WHEN 16 THEN 16 END ) WHEN 16 THEN 16 WHEN 0 THEN 0 END + CASE max( CASE bitand( BOOK.ID, 32 ) WHEN 0 THEN 0 WHEN 32 THEN 32 END ) WHEN 32 THEN 32 WHEN 0 THEN 0 END + CASE max( CASE bitand( BOOK.ID, 64 ) WHEN 0 THEN 0 WHEN 64 THEN 64 END ) WHEN 64 THEN 64 WHEN 0 THEN 0 END + CASE max( CASE bitand( BOOK.ID, -128 ) WHEN 0 THEN 0 WHEN -128 THEN -128 END ) WHEN -128 THEN -128 WHEN 0 THEN 0 END)) - 1)
Oracle
((0 - bit_or_agg(BOOK.ID)) - 1)
Snowflake
bitnot(bit_or_agg(BOOK.ID))
Trino
bitwise_not(bitwise_or_agg(BOOK.ID))
Vertica
~(hex_to_integer(to_hex(bit_or(hex_to_binary(to_hex(BOOK.ID))))))
Access, Derby
/* UNSUPPORTED */
Generated with jOOQ 3.21. Support in older jOOQ versions may differ. Translate your own SQL on our website
Feedback
Do you have any feedback about this page? We'd love to hear it!