Folks

I am in the process of moving a decade old back-end from DB2 9.5 to Oracle 19c.

I frequently see in SQL queries and veiw definitions bizarre timestamp(nullif('','')) constructs used instead of a plain null.

What is the point of doing so? Why would anyone in their same mind would want to do so?

Disclaimer: my SQL skills are fairly mediocre. I might well miss something obvious.

CodePudding user response：

It is not clear why - in any SQL dialect, no matter how old - one would use an argument like nullif('',''). Regardless of the result, that is a constant that can be calculated once and for all, and given as argument to timestamp(). Very likely, it should be null in any dialect and any version. So that should be the same as timestamp(null). The code you found suggests that whoever wrote it didn't know what they were doing.

One might need to write something like that - rather than a plain null - to get null of a specific data type. Even though "theoretical" SQL says null does not have a data type, you may need something like that, for example in a view, to define the data type of the column defined by an expression like that.

In Oracle you can use the cast() function, as MT0 demonstrated already - that is by far the most common and most elegant equivalent.

If you want something much closer in spirit to what you saw in that old code, to_timestamp(null) will have the same effect. No reason to write something more complicated for null given as argument, though - along the lines of that nullif() call.

CodePudding user response：

It appears to create a NULL value with a TIMESTAMP data type.

The TIMESTAMP DB2 documentation states:

TIMESTAMP scalar function

The TIMESTAMP function returns a timestamp from a value or a pair of values.

TIMESTAMP(expression1, [expression2])

expression1 and expression2

The rules for the arguments depend on whether expression2 is specified and the data type of expression2.

• If only one argument is specified it must be an expression that returns a value of one of the following built-in data types: a DATE, a TIMESTAMP, or a character string that is not a CLOB.

If you try to pass an untyped NULL to the TIMESTAMP function:

TIMESTAMP(NULL)

Then you get the error:

The invocation of routine "TIMESTAMP" is ambiguous. The argument in position "1" does not have a best fit.

To invoke the function, you need to pass one of the required DATE, TIMESTAMP or a non-CLOB string to the function which means that you need to coerce the NULL to have one of those types.

This could be:

TIMESTAMP(CAST(NULL AS VARCHAR(14)))
TIMESTAMP(NULLIF('',''))

Using NULLIF is more confusing but, if I have to try to make an excuse for using it, is slightly less to type than casting a NULL to a string.

The equivalent in Oracle would be:

CAST(NULL AS TIMESTAMP)

This also works in DB2 (and is even less to type).