Or Not

Definition

The orNot methods allows you to add the OR NOT operator to a HAVING clause.

Available methods

1. orNot(KCondition kCondition)

• kCondition: which contains all the information about the condition that will be added to the HAVING clause with an OR NOT operator.

2. orNot(KRaw kRaw)

• kRaw: is a raw content which will be added in the HAVING clause with an OR NOT operator.

Method hierarchy

The orNot method can be used right after the following methods:

• having, and, andNot, or, orNot

and the subsequent methods that can be called are:

• and, andNot, or, orNot, window, except, exceptAll, intersect, intersectAll, union, unionAll, orderBy, limit, offset, fetch, single, multiple

Example: KCondition

Java code:

k
.select(
    count().as("all"),
    APP_USER.EMAIL.countDistinct().as("distinctEmail"),
    toChar(APP_USER.CREATED_AT, "YYYY").as("year")
)
.from(APP_USER)
.groupBy(raw("year"))
.having(count().greaterThan(120))
.orNot(APP_USER.EMAIL.countDistinct().greaterThan(70))
.multiple();

SQL generated:

SELECT
    COUNT(*) AS "all",
    COUNT(DISTINCT au.email) AS "distinctEmail",
    TO_CHAR(au.created_at, ?1) AS "year"
FROM app_user au
GROUP BY year
HAVING COUNT(*) > ?2
OR NOT (COUNT(DISTINCT au.email) > ?3)

Parameters:

• ?1: "YYYY
• ?2: 120
• ?3: 70

Example: KRaw

Java code:

k
.select(
    count().as("all"),
    APP_USER.EMAIL.countDistinct().as("distinctEmail"),
    toChar(APP_USER.CREATED_AT, "YYYY").as("year")
)
.from(APP_USER)
.groupBy(raw("year"))
.having(raw("COUNT(*) > 120"))
.orNot(raw("COUNT(DISTINCT au.email) > 70"))
.multiple();

SQL generated:

SELECT
    COUNT(*) AS "all",
    COUNT(DISTINCT au.email) AS "distinctEmail",
    TO_CHAR(au.created_at, ?1) AS "year"
FROM app_user au
GROUP BY year
HAVING COUNT(*) > 120
OR NOT (COUNT(DISTINCT au.email) > 70)

Parameters:

• ?1: "YYYY