Syntax for a SELECT which checks if column field of type string is '' or null

[nocin]

Hi CAP Team,

a field of type String is per default on the HANA DB null. But once it was filled and is then cleared again, it contains an empty String instead of null. Can you add some documentation on how to handle such situations in a single SELECT statement that should simply check if the column is inital?

My current coding seems unnecessarily long and complicated...

    const exists = await SELECT(1).from(Entity).where({
        stringProperty: null
    })

    const exists2 = await SELECT(1).from(Entity).where({
        stringProperty: ''
    })
    return (exists.length > 0 || exists2.length > 0) ? true : false

From my tests, it seems that null cannot be combined with in, like this: const exists = await SELECT(1).from(Entity).where({ stringProperty: {"in": [null, '']} })

Thanks, nico

[renejeglinsky]

Thanks Nico, I'll get back to you soon.

[AjitKP91]

@nocin Does not work: const exists = await SELECT(1).from(Entity).where({or:{stringProperty: null, stringProperty: ''}) const exists = await SELECT(1).from(Entity).where({ stringProperty: {"in": [null, '']} })

Surprisingly works: const exists = await SELECT(1).from(Entity).where(`stringProperty = null or stringProperty = ''`) Can you try this?

[nocin]

Hi @AjitKP91,

thanks for your suggestion! I would have expected both "or" queries should result in the same CQN.

const exists = await SELECT(1).from(Entity).where({or:{stringProperty: null, stringProperty: ''})
const exists = await SELECT(1).from(Entity).where(`stringProperty = null or stringProperty = ''`)

Just tried to rewrite my real query from the fluent API syntax to the string literal representation. Had to adjust the date's as well, to prevent syntax errors. But unfortunately it does not return the expected result.

        const exists = await SELECT(1).from(Entity).where({
            userId: userId,
            validFrom: { '<=': new Date().toISOString() },
            validTo: { '>=': new Date().toISOString() },
            stringProperty : null
        })

to

    const exists = await SELECT(1).from(Entity).where(`
        userId = ${userId} and
        validFrom <= ${new Date().toISOString().substring(0,10)} and 
        validTo >= ${new Date().toISOString().substring(0,10)} and 
        ( stringProperty = null or stringProperty = '' )
    `)

But maybe that's because something else is wrong in the syntax. Not very experienced with the string literal representation.

In general, I have to say, the way HANA works is a bit cumbersome, but I guess there are some use cases where a differentiation for null and '' in string fields is needed. And perhaps we have an issue in the cds.ql logic of the CAP framework, because the "or" condition does not result in the same CQN?! It would be helpful to have something like we have in ABAP: stringProperty IS INITIAL :-)

BR, Nico

[AjitKP91]

@nocin Completely agree with you. Easier approach is required for initial check of fields based on data type.

[nocin]

Hi @renejeglinsky,

it seems that the topic is not only relevant for the documentation, but also a more general problem with optimization potential. Can you address the topic internally?

Thanks, Nico

[renejeglinsky]

Already in it's way :) Me or one of my colleagues will get back to you.

[johannes-vogel]

Hi @nocin,

I'll try to point out two solutions.

There's a dramatic difference between these two that I'll try to explain. First is using tagged templates, second is using simple string literals.

In the first version, the values within ${} are passed as additional arguments to the where function and we can identify that those are values. In second variant, the string is constructed first, hence all quoting for values has to be applied manually.

SELECT(1).from(Entity).where` // variant 1
        userId = ${userId} and
        validFrom <= ${new Date().toISOString().substring(0,10)} and 
        validTo >= ${new Date().toISOString().substring(0,10)} and 
        ( stringProperty = null or stringProperty = '' )
    `

SELECT(1).from(Entity).where(` // variant 2
        userId = ${userId} and
        validFrom <= '${new Date().toISOString().substring(0,10)}' and 
        validTo >= '${new Date().toISOString().substring(0,10)}' and 
        ( stringProperty = null or stringProperty = '' )
    `)

In general, I do believe that complex expressions are easier to understand in fluent sql-like syntax.

[nocin]

Hi @johannes-vogel,

thanks for this explanation! I was not aware of the difference between .where`` (tagged template) and .where(``) (string literal). I got it now working using variant 2. Only had to add quotes for ${userId} as well.

    const exists = await SELECT(1).from(Entity).where(`
        userId = '${userId}' and
        validFrom <= '${new Date().toISOString().substring(0, 10)}' and 
        validTo >= '${new Date().toISOString().substring(0, 10)}' and 
        ( stringProperty = null or stringProperty = '' )
    `)
    return exists.length > 0 ? true : false

But I would also prefer to stick to the "json" fluent sql syntax, if this would be possible. All in all, it still seems a bit complicated for such a simple sql query.

BR, Nico

[johannes-vogel]

that's also possible:

        const exists = await SELECT(1).from(Entity).where({
            userId: userId,
            validFrom: { '<=': new Date().toISOString() },
            validTo: { '>=': new Date().toISOString() },
            and:{stringProperty: null, or: {stringProperty: ''}}
        })

However as mentioned above not very easy to understand. The ored expression is concatenated with and to previous epressions.