Available in versions: Dev (3.19) | Latest (3.18) | 3.17

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 position p if the argument is 1 at position p for at least one row in the group.
1 at position p if the argument is 0 at position p 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, SQLITE, SQLSERVER
~((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, DUCKDB, MARIADB, MYSQL, POSTGRES, SYBASE, YUGABYTEDB
~(bit_or(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 */

(These are currently generated with jOOQ 3.19, see #10141), or translate your own on our website

previous : next

Feedback

Do you have any feedback about this page? We'd love to hear it!

Tell us a little bit about yourself and join our newsletter (examples)

BIT_NOR_AGG

Dialect support

Feedback

Community

Support

Legal

Documentation