What is the correct way to create a NULL valued expression? Let's say I want a branch of a case/when...

# exposed

[Mranders]

What is the correct way to create a NULL valued expression? Let's say I want a branch of a case/when/else to result in NULL.

[Kenneth Wußmann]

Hey, not quite sure if I got your question right, but you can do something like this:

Person.find {
    when (something) {
        "ABC" -> Persons.age eq 21
        else -> Persons.age.isNull()
    }
}

[Mranders]

I'm referring to an SQL case statement (using SqlExpressionBuilder), like this:

private fun SqlExpressionBuilder.filteredString(expr: Expression<String?>): Expression<String?> {
    return case()
            .When(expr neq stringLiteral("foo"), expr)
            .Else(???)
}

What is an appropriate way to fill the else part? The isNull extension returns an Op<Boolean>, which can't be used in this case.

[Kenneth Wußmann]

Hm, not sure if there is one included but you can write one yourself:

fun <T> nullExpr() = object : Expression<T>() {
    override fun toQueryBuilder(queryBuilder: QueryBuilder) {
        queryBuilder {
            append("NULL")
        }
    }
}

[Mranders]

I was just wondering if there was some built in function to do it. I ended up creating the following function:

private fun <T> nullLiteral(columnType: IColumnType): LiteralOp<T?> =
        LiteralOp(columnType.apply { nullable = true }, null)

[tapac]

I would advice to use @Kenneth Wußmann code as your function will mutate original columnType and if you call it like nullLiteral(MyTable.notNullColumn) you will end up with wrong behavior on notNullColumn.

I will add similar code into the next release.