The jOOQ User Manual : SQL building : Column expressions : Aggregate functions : BIT_NAND_AGG | previous : next |
BIT_NAND_AGG
Applies to ✅ Open Source Edition ✅ Express Edition ✅ Professional Edition ✅ Enterprise Edition
An aggregate function to perform the equivalent of the BIT_NAND function on a data set. In other words, the resulting bits are:
-
0
at positionp
if the argument is1
at positionp
for every row in the group. -
1
at positionp
if the argument is0
at positionp
for at least one row in the group.
As with most aggregate functions, NULL
values are not aggregated.
SELECT bit_nand_agg(ID), bit_nand_agg(AUTHOR_ID) FROM BOOK
create.select( bitNandAgg(BOOK.ID), bitNandAgg(BOOK.AUTHOR_ID)) .from(BOOK)
Producing:
+--------------+--------------+ | bit_nand_agg | bit_nand_agg | +--------------+--------------+ | -1 | -1 | +--------------+--------------+
Dialect support
This example using jOOQ:
bitNandAgg(BOOK.ID.coerce(TINYINT))
Translates to the following dialect specific expressions:
-- ASE, SQLDATAWAREHOUSE, SQLSERVER ~((CASE WHEN CASE WHEN min(CASE WHEN (BOOK.ID & 1) <> 0 THEN 1 ELSE 0 END) = 1 THEN 1 WHEN NOT (min(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 min(CASE WHEN (BOOK.ID & 2) <> 0 THEN 1 ELSE 0 END) = 1 THEN 1 WHEN NOT (min(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 min(CASE WHEN (BOOK.ID & 4) <> 0 THEN 1 ELSE 0 END) = 1 THEN 1 WHEN NOT (min(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 min(CASE WHEN (BOOK.ID & 8) <> 0 THEN 1 ELSE 0 END) = 1 THEN 1 WHEN NOT (min(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 min(CASE WHEN (BOOK.ID & 16) <> 0 THEN 1 ELSE 0 END) = 1 THEN 1 WHEN NOT (min(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 min(CASE WHEN (BOOK.ID & 32) <> 0 THEN 1 ELSE 0 END) = 1 THEN 1 WHEN NOT (min(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 min(CASE WHEN (BOOK.ID & 64) <> 0 THEN 1 ELSE 0 END) = 1 THEN 1 WHEN NOT (min(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 min(CASE WHEN (BOOK.ID & -128) <> 0 THEN 1 ELSE 0 END) = 1 THEN 1 WHEN NOT (min(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 ~(bit_and_agg(BOOK.ID)) -- BIGQUERY, COCKROACHDB, MARIADB, MYSQL, POSTGRES, REDSHIFT, SYBASE, VERTICA, YUGABYTEDB ~(bit_and(BOOK.ID)) -- DB2, TERADATA bitnot((CASE WHEN CASE WHEN min(CASE WHEN bitand( BOOK.ID, 1 ) <> 0 THEN 1 ELSE 0 END) = 1 THEN 1 WHEN NOT (min(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 min(CASE WHEN bitand( BOOK.ID, 2 ) <> 0 THEN 1 ELSE 0 END) = 1 THEN 1 WHEN NOT (min(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 min(CASE WHEN bitand( BOOK.ID, 4 ) <> 0 THEN 1 ELSE 0 END) = 1 THEN 1 WHEN NOT (min(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 min(CASE WHEN bitand( BOOK.ID, 8 ) <> 0 THEN 1 ELSE 0 END) = 1 THEN 1 WHEN NOT (min(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 min(CASE WHEN bitand( BOOK.ID, 16 ) <> 0 THEN 1 ELSE 0 END) = 1 THEN 1 WHEN NOT (min(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 min(CASE WHEN bitand( BOOK.ID, 32 ) <> 0 THEN 1 ELSE 0 END) = 1 THEN 1 WHEN NOT (min(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 min(CASE WHEN bitand( BOOK.ID, 64 ) <> 0 THEN 1 ELSE 0 END) = 1 THEN 1 WHEN NOT (min(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 min(CASE WHEN bitand( BOOK.ID, -128 ) <> 0 THEN 1 ELSE 0 END) = 1 THEN 1 WHEN NOT (min(CASE WHEN bitand( BOOK.ID, -128 ) <> 0 THEN 1 ELSE 0 END) = 1) THEN 0 END = 1 THEN -128 ELSE 0 END)) -- FIREBIRD bin_not((CASE WHEN CASE WHEN min(CASE WHEN bin_and( BOOK.ID, 1 ) <> 0 THEN 1 ELSE 0 END) = 1 THEN 1 WHEN NOT (min(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 min(CASE WHEN bin_and( BOOK.ID, 2 ) <> 0 THEN 1 ELSE 0 END) = 1 THEN 1 WHEN NOT (min(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 min(CASE WHEN bin_and( BOOK.ID, 4 ) <> 0 THEN 1 ELSE 0 END) = 1 THEN 1 WHEN NOT (min(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 min(CASE WHEN bin_and( BOOK.ID, 8 ) <> 0 THEN 1 ELSE 0 END) = 1 THEN 1 WHEN NOT (min(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 min(CASE WHEN bin_and( BOOK.ID, 16 ) <> 0 THEN 1 ELSE 0 END) = 1 THEN 1 WHEN NOT (min(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 min(CASE WHEN bin_and( BOOK.ID, 32 ) <> 0 THEN 1 ELSE 0 END) = 1 THEN 1 WHEN NOT (min(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 min(CASE WHEN bin_and( BOOK.ID, 64 ) <> 0 THEN 1 ELSE 0 END) = 1 THEN 1 WHEN NOT (min(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 min(CASE WHEN bin_and( BOOK.ID, -128 ) <> 0 THEN 1 ELSE 0 END) = 1 THEN 1 WHEN NOT (min(CASE WHEN bin_and( BOOK.ID, -128 ) <> 0 THEN 1 ELSE 0 END) = 1) THEN 0 END = 1 THEN -128 ELSE 0 END)) -- H2 bit_nand_agg(BOOK.ID) -- HANA ~((CASE WHEN CASE WHEN min(CASE WHEN bitand( BOOK.ID, 1 ) <> 0 THEN 1 ELSE 0 END) = 1 THEN TRUE WHEN NOT (min(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 min(CASE WHEN bitand( BOOK.ID, 2 ) <> 0 THEN 1 ELSE 0 END) = 1 THEN TRUE WHEN NOT (min(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 min(CASE WHEN bitand( BOOK.ID, 4 ) <> 0 THEN 1 ELSE 0 END) = 1 THEN TRUE WHEN NOT (min(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 min(CASE WHEN bitand( BOOK.ID, 8 ) <> 0 THEN 1 ELSE 0 END) = 1 THEN TRUE WHEN NOT (min(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 min(CASE WHEN bitand( BOOK.ID, 16 ) <> 0 THEN 1 ELSE 0 END) = 1 THEN TRUE WHEN NOT (min(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 min(CASE WHEN bitand( BOOK.ID, 32 ) <> 0 THEN 1 ELSE 0 END) = 1 THEN TRUE WHEN NOT (min(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 min(CASE WHEN bitand( BOOK.ID, 64 ) <> 0 THEN 1 ELSE 0 END) = 1 THEN TRUE WHEN NOT (min(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 min(CASE WHEN bitand( BOOK.ID, -128 ) <> 0 THEN 1 ELSE 0 END) = 1 THEN TRUE WHEN NOT (min(CASE WHEN bitand( BOOK.ID, -128 ) <> 0 THEN 1 ELSE 0 END) = 1) THEN FALSE END = TRUE THEN -128 ELSE 0 END)) -- HSQLDB ((0 - (CASE WHEN (min(CASE WHEN bitand( BOOK.ID, 1 ) <> 0 THEN 1 ELSE 0 END) = 1) THEN 1 ELSE 0 END + CASE WHEN (min(CASE WHEN bitand( BOOK.ID, 2 ) <> 0 THEN 1 ELSE 0 END) = 1) THEN 2 ELSE 0 END + CASE WHEN (min(CASE WHEN bitand( BOOK.ID, 4 ) <> 0 THEN 1 ELSE 0 END) = 1) THEN 4 ELSE 0 END + CASE WHEN (min(CASE WHEN bitand( BOOK.ID, 8 ) <> 0 THEN 1 ELSE 0 END) = 1) THEN 8 ELSE 0 END + CASE WHEN (min(CASE WHEN bitand( BOOK.ID, 16 ) <> 0 THEN 1 ELSE 0 END) = 1) THEN 16 ELSE 0 END + CASE WHEN (min(CASE WHEN bitand( BOOK.ID, 32 ) <> 0 THEN 1 ELSE 0 END) = 1) THEN 32 ELSE 0 END + CASE WHEN (min(CASE WHEN bitand( BOOK.ID, 64 ) <> 0 THEN 1 ELSE 0 END) = 1) THEN 64 ELSE 0 END + CASE WHEN (min(CASE WHEN bitand( BOOK.ID, -128 ) <> 0 THEN 1 ELSE 0 END) = 1) THEN -128 ELSE 0 END)) - 1) -- INFORMIX bitnot((CASE WHEN (min(CASE WHEN bitand( BOOK.ID, 1 ) <> 0 THEN 1 ELSE 0 END) = 1) THEN 1 ELSE 0 END + CASE WHEN (min(CASE WHEN bitand( BOOK.ID, 2 ) <> 0 THEN 1 ELSE 0 END) = 1) THEN 2 ELSE 0 END + CASE WHEN (min(CASE WHEN bitand( BOOK.ID, 4 ) <> 0 THEN 1 ELSE 0 END) = 1) THEN 4 ELSE 0 END + CASE WHEN (min(CASE WHEN bitand( BOOK.ID, 8 ) <> 0 THEN 1 ELSE 0 END) = 1) THEN 8 ELSE 0 END + CASE WHEN (min(CASE WHEN bitand( BOOK.ID, 16 ) <> 0 THEN 1 ELSE 0 END) = 1) THEN 16 ELSE 0 END + CASE WHEN (min(CASE WHEN bitand( BOOK.ID, 32 ) <> 0 THEN 1 ELSE 0 END) = 1) THEN 32 ELSE 0 END + CASE WHEN (min(CASE WHEN bitand( BOOK.ID, 64 ) <> 0 THEN 1 ELSE 0 END) = 1) THEN 64 ELSE 0 END + CASE WHEN (min(CASE WHEN bitand( BOOK.ID, -128 ) <> 0 THEN 1 ELSE 0 END) = 1) THEN -128 ELSE 0 END)) -- MEMSQL, SQLITE ~((CASE WHEN (min(CASE WHEN (BOOK.ID & 1) <> 0 THEN 1 ELSE 0 END) = 1) THEN 1 ELSE 0 END + CASE WHEN (min(CASE WHEN (BOOK.ID & 2) <> 0 THEN 1 ELSE 0 END) = 1) THEN 2 ELSE 0 END + CASE WHEN (min(CASE WHEN (BOOK.ID & 4) <> 0 THEN 1 ELSE 0 END) = 1) THEN 4 ELSE 0 END + CASE WHEN (min(CASE WHEN (BOOK.ID & 8) <> 0 THEN 1 ELSE 0 END) = 1) THEN 8 ELSE 0 END + CASE WHEN (min(CASE WHEN (BOOK.ID & 16) <> 0 THEN 1 ELSE 0 END) = 1) THEN 16 ELSE 0 END + CASE WHEN (min(CASE WHEN (BOOK.ID & 32) <> 0 THEN 1 ELSE 0 END) = 1) THEN 32 ELSE 0 END + CASE WHEN (min(CASE WHEN (BOOK.ID & 64) <> 0 THEN 1 ELSE 0 END) = 1) THEN 64 ELSE 0 END + CASE WHEN (min(CASE WHEN (BOOK.ID & -128) <> 0 THEN 1 ELSE 0 END) = 1) THEN -128 ELSE 0 END)) -- ORACLE ((0 - bit_and_agg(BOOK.ID)) - 1) -- SNOWFLAKE bitnot(bit_and_agg(BOOK.ID)) -- 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!