CODEWITHSUNDEEP
sqldatabasedb2
JoshKni8
JoshKni8

Reputation: 155

When date is Null return another value? - DB2 database

I am trying to write a query which returns a specific date when the result is null.

My attempt:

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

I am getting the error DB2 SQL Error:

SQLCODE=-199, SQLSTATE=42601, SQLERRMC=AS;END OVER CONCAT || / - + AT * YEAR YEARS MONTH MONTHS DAY DAYS

Why is this?

Upvotes: 0

Views: 8026

Answers (2)

Dan Bracuk
Dan Bracuk

Reputation: 20804

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.

Upvotes: 3

Gordon Linoff
Gordon Linoff

Reputation: 1269893

You don't need trim() if you are checking for NULL values. So, consider something like:

SELECT COALESCE(t1.COLUMN_NAME, '9999-12-31')

Your question is actually why you are getting that error. Well, basically it is listing some possible tokens that can appear between the '9999-12-31' and whatever comes next. One of these is END (although it is missing ELSE as a possibility).

Upvotes: 4

Related Questions