SQL CASE expression gives error message with calculation

Question

I have a query, where I have to compare Due Dates and I can't seem to get my CASE expression to work properly.

At the moment I have commented out the original ELSE clause, which I thought could do the trick, but unfortunately, it just gives me an error "Argument 1 of function CASE not valid. Cause . . . . . : The data type, length, or value of argument 1 of function CASE specified is not valid."

Any ideas? I'm operating on an IBM DB2.

The desired result for the ELSE clause would be to add the Invoice date (AHINVD) and Invoice Due Date Code (CMDDCD) to get the Original Invoice Due Date.

    Select
CMCMP as CompanyCode
,CMCUS# as CustomerCode
,CMNAME as CustomerName
,CMDDCD as InvoiceDuteDateCode
,AHINV# as InvoiceNumber
,AHISEQ as InvoiceSequence
,AHINVD as InvoiceDate
,AHDUED as InvoiceDueDate
,case
    when date(to_date(RTRIM(AHINVD),'YYYYMMDD')) + cast(CMDDCD as INT) days = date(to_date(RTRIM(AHDUED),'YYYYMMDD')) then NULL
    --else date(to_date(RTRIM(AHINVD),'YYYYMMDD')) + cast(CMDDCD as INT) days
    else '12'
end as OriginalInvoiceDueDate
,AHAMT$ as OriginalAmount
,AHCURC as CurrencyCode
from WSDATARG.CUSMASFL cm
left join WSDATARG.ARIHDRFL ar
on CMCMP = AHCMP
and CMCUS# = AHCUS#
where CMCMP = '14'
and AHINVD > '20180918

What platform (z/OS, LUW or i) and version of DB2 are you using? — Paul Vernon, Sep 19 '18 at 14:29

score 1 · Answer 1 · answered Sep 19 '18 at 14:32

1

Try CAST(NULL AS DATE) instead of just NULL in the case

DB2: Won't Allow "NULL" column?

answered Sep 19 '18 at 14:32

Paul Vernon

3,818
1
10
23

The NULL is not the problem in this CASE expression, the actual problem lies with the ELSE part of the expression with adding the Invoice Date with Invoice Due Date Code, which for some reason do not work and the query gives an error message. If I have replaced the ELSE clause with a dummy ELSE clause like " else '12' ", then the query runs. But if I try to use the else date(to_date(RTRIM(AHINVD),'YYYYMMDD')) + cast(CMDDCD as INT) days Then the query produces an error. I cannot seem to figure out whats wrong. – Jaanis Veinberg Sep 19 '18 at 18:09
What version of Db2 are you using? What is the SQL error code? – Paul Vernon Sep 19 '18 at 18:34
Did you try my CAST with your ELSE? – Paul Vernon Sep 19 '18 at 18:59

score 0 · Accepted Answer · answered Sep 19 '18 at 20:56

I suspect you are using DB2 for z/OS or DB2 for i. If so, I suspect it will not like the un-typed NULL. Db2 LUW will workout it needs to be a DATE. e.g.

db2 "CREATE TABLE TEST(AHINVD CHAR(8), CMDDCD CHAR(6), AHDUED CHAR(8))"
db2 "describe 
SELECT 
case
    when date(to_date(RTRIM(AHINVD),'YYYYMMDD')) + cast(CMDDCD as INT) days = date(to_date(RTRIM(AHDUED),'YYYYMMDD')) then NULL
    else date(to_date(RTRIM(AHINVD),'YYYYMMDD')) + cast(CMDDCD as INT) days
end
FROM TEST"


 Column Information

 Number of columns: 1

 SQL type              Type length  Column name                     Name length
 --------------------  -----------  ------------------------------  -----------
 385   DATE                     10  1                                         1

but e.g. this

SELECT 
case
    when date(to_date(RTRIM(AHINVD),'YYYYMMDD')) + cast(CMDDCD as INT) days = date(to_date(RTRIM(AHDUED),'YYYYMMDD')) then CAST(NULL AS DOUBLE)
    else date(to_date(RTRIM(AHINVD),'YYYYMMDD')) + cast(CMDDCD as INT) days
end
FROM TEST

returns

‬‪The‬‎ ‪data‬‎ ‪types‬‎ ‪of‬‎ ‪the‬‎ ‪result‬‎-‪expressions‬‎ ‪of‬‎ ‪a‬‎ ‪CASE‬‎ ‪expression‬‎ ‪or‬‎ ‪DECODE‬‎ ‪function‬‎ ‪are‬‎ ‪not‬‎ ‪compatible‬‎.‪‬‎.‪‬‎ ‪SQLCODE‬‎=‪‬‎-‪581‬‎,‪‬‎ ‪SQLSTATE‬‎=‪42804‬‎

Which I'm guessing is similar to your Db2 for z/OS message

Maybe, as NULL is what you get if you don't have an ELSE, why not just code this ?

SELECT 
case
    when  date(to_date(RTRIM(AHINVD),'YYYYMMDD')) + cast(CMDDCD as INT) days 
       <> date(to_date(RTRIM(AHDUED),'YYYYMMDD'))
    then  date(to_date(RTRIM(AHINVD),'YYYYMMDD')) + cast(CMDDCD as INT) days
end
FROM TEST

I tried the last code example you wrote and it works. For some reason, if I incorporate and ELSE part into it, code doesn't work. Now with ELSE out of the picture, it works. Which is fantastic. But would you happen to know, why is it like this? Why the code works without having ELSE in the CASE expression? — Jaanis Veinberg, Sep 20 '18 at 05:27
When Db2 has a `NULL` literal without any explicit casting it will default it's datatype to `VARCHAR(1)`. In a CASE statement, Db2 LUW will override this default based on the datatype of the next branch of the CASE. It looks like Db2 for zOS (or iSeries) might not do that, but simply reports that the `VARCHAR(1)` is not compatible with the `DATE` you have in the `ELSE`, giving the "Argument 1 of function CASE not valid" error. When the `DATE` datatype is the first branch of the CASE, the `NULL` from the (implicit or explicit) `ELSE` can be cast to a `DATE` by DB2, and hence no error. — Paul Vernon, Sep 20 '18 at 09:44

SQL CASE expression gives error message with calculation

2 Answers2