I am trying to write a case to get a specific date value in a field, but keep getting errors.
My case looks like this:
CASE WHEN TEP ='QC' AND (CHECKFLOW IN ('Process') THEN DATE ELSE 0 END AS QCStopTime
My SQL example:
SELECTDISTINCT ID, STEP ,FLOW, DATE,
CASEWHEN STEP ='QC 'AND (CHECKFLOW IN ('Process’)) THEN DATE ELSE 0 END AS QCStopTime
AND MESSAGE =‘COMPLETED’
AND ACTIONTIME > 0
Each time I write the query, I get an error that states:
DB2 Database Error: ERROR  [IBM][DB2/LINUXX8664] SQL0581N The data types of the result-expressions of a CASE expression or DECODE function are not compatible
Are cases not allowed or able to reference a field?
Thoughts on how to write this better?
Thanks in advance for your help.
Answer by Kev Riley ·
Looks like this is DB2 question, and whilst we are primarily a SQL Server focused forum, I'll try to help.
The datatype of the output column QCStopTime is what is causing the issue - the system doesn't know what datatype this is supposed to be as you are using both integer and a date-time, hence the error message that the result expressions are not compatible.
If you want the output to appear as you've typed it, then this would have to be converted some way into a string.