0

I have a problem with a query in Db2. I need to get the MAX value from a date value. If it is Null it should return 9999-12-31 .

Below is my query so far:

select
  case
    when max(cancel_dt) is null
    then '9999-12-31'
    else max(cancel_dt)
from table
where <conditions>
data_henrik
  • 16,724
  • 2
  • 28
  • 49
Lovely Kolli
  • 1
  • 1
  • What is the error if any? – data_henrik Nov 02 '20 at 08:24
  • 1. You've missed `END` at end of `CASE` statement, so this will not work. 2. You can use `COALESCE` function instead of `CASE`. – astentx Nov 02 '20 at 08:26
  • Sample data and desired results would help. Do you mean if *all* values are `NULL` (i.e. the result of the `max()` is null) or *any* values are `NULL`? What is the issue with your query? – Gordon Linoff Nov 02 '20 at 12:36

2 Answers2

0

I think you're missing END after MAX(CAncel_DT) to close your CASE expression.

Personally I'd probably write it as :

SELECT
ISNULL(MAX(Cancel_dt), '9999-12-31') as [MAX_DATE]
FROM table
WHERE conditions

NB as an aside, thanks to @jarlh's comment I looked up the difference between CASE statement and CASE expression and found the following very useful: Case Expression vs Case Statement

Joe Shark
  • 688
  • 4
  • 9
-1

Try it the other way round - the CASE within the MAX - like

SELECT max( case when Cancel_dt IS NULL then '9999-12-31' 
                 else Cancel_dt
             end)
 FROM table where conditions

You could also use coalesce instead oif the case statement:

coalesce(Cancel_dt, '9999-12-31')

Coalesce will use the scond value in case the first is NULL - so exactly what you want. The coalesce should be inside the max then of cause.

MichaelTiefenbacher
  • 3,805
  • 2
  • 11
  • 17
  • 3
    This query will return '9999-12-31' if any null value exists, but the requrement is to return this value if max value is null. Aggregate functions ignore nulls and can return it only if all the values are nulls (or no rows were found by filter criteria). – astentx Nov 02 '20 at 08:32
  • case _expression_, not statement. – jarlh Nov 02 '20 at 08:39
  • I think this is the correct interpretation of the question. – Gordon Linoff Nov 02 '20 at 12:36
  • @astentx my answer is correct for the question asked. Of cause your comment is right but this was not the question. So I do not understand why my answer was downrated! – MichaelTiefenbacher Nov 02 '20 at 18:57
  • @MichaelTiefenbacher Because max(isnull(somedate, '9999-12-31')) <> isnull(max(somedate, '9999-12-31')). The question directly states: "I need max value... If **it** is null, **it** should return '9999-12-31'". There's no mention of *any* null and moreover, max(somedate) can return null if all the values are not null if you apply where condition. So I do not see posibilities here. – astentx Nov 02 '20 at 22:21