Isnull Function in Db2 SQL

When date is Null return another value? - DB2 database

You are missing the keyword end

this:

CASE WHEN TRIM(t1.COLUMN_NAME) is null THEN '9999-12-31'

should be this:

CASE WHEN TRIM(t1.COLUMN_NAME) is null THEN '9999-12-31' else t1.column_name end

Also, you don't need the trim function. trim(null) is null. Plus, this assumes your field is char or varchar. If it's a date, you can't use trim. Also, make sure your default value is a valid date in db2.

Using ISNULL vs using COALESCE for checking a specific condition?

This problem reported on Microsoft Connect reveals some differences between COALESCE and ISNULL:

an early part of our processing rewrites COALESCE( expression1, expression2 ) as CASE WHEN expression1 IS NOT NULL THEN expression1 ELSE expression2 END. In [this example]:

COALESCE ( ( SELECT Nullable
             FROM Demo
             WHERE SomeCol = 1 ), 1 )

we generate:

SELECT CASE
          WHEN (SELECT Nullable FROM Demo WHERE SomeCol = 1) IS NOT NULL
          THEN (SELECT Nullable FROM Demo WHERE SomeCol = 1)
          ELSE 1
       END

Later stages of query processing don't understand that the two subqueries were originally the same expression, so they execute the subquery twice...

One workaround, though I hate to suggest it, is to change COALESCE to ISNULL, since the latter doesn't duplicate the subquery.

DB2 NVL with empty string

You could combine it with NULLIF:

The NULLIF function returns the null value if the two arguments are equal; otherwise, it returns the value of the first argument.

SELECT NVL(NULLIF(col, ''),'replacement') 

SQL Select statement returning NULL

from the comments

HHHCRIN is 'Y' or 'N', HHHINVN is defined as S 7,0 it is an invoice
number.

You can't return a string, be it blanks or 'N/A' when the return column is numeric.

Since Db2 can't implicitly convert 'N/A' to a number, you get null.

Try returning all strings...

   CASE HHHCRIN
   WHEN 'Y' THEN char(HHHINVN) ELSE 'N/A'
   END AS "Credit Memo Document Number",

Null Results From TIMESTAMPDIFF in DB2 SQL

When you subtract dates or timestamps, you end up with a duration..

A duration is a number formatted as yyyymmddhhmmss.zzzzzzzzzzzz.

From the manual:

A timestamp duration represents a number of years, months, days, hours, minutes, seconds, and
fractional seconds, expressed as a DECIMAL(14+s,s) number, where s is the number of digits of
fractional seconds ranging from 0 to 12. To be properly interpreted, the number must have the format
yyyymmddhhmmss.zzzzzzzzzzzz, where yyyy, mm, dd, hh, mm, ss, and zzzzzzzzzzzz represent,
respectively, the number of years, months, days, hours, minutes, seconds, and fractional seconds. The
result of subtracting one timestamp value from another is a timestamp duration with scale that
matches the maximum timestamp precision of the timestamp operands.

select timestamp('2022-09-12 15:59:14.548636') - timestamp('2022-09-12 14:56:10.791140') from sysibm.SYSDUMMY1;

returns 10303.757496

And is read as 1 hour, 3 minutes, 3.757496 seconds

So if you wanted to, you can do the math yourself. Better yet build your own UDF that returns a big integer or even larger as a decimal value.

---

Related Topics