The jOOQ User Manual : SQL building : Column expressions : Aggregate functions : BIT_NAND_AGGprevious : next

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

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 position p if the argument is 1 at position p for every row in the group.
  • 1 at position p if the argument is 0 at position p 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


The jOOQ User Manual : SQL building : Column expressions : Aggregate functions : BIT_NAND_AGGprevious : next

Feedback

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

Community

Support

Legal

Documentation

The jOOQ Logo