question

SQL_User321 avatar image
SQL_User321 asked

SQL Help

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

fromMyTable

where ID='ABC123'

AND MESSAGE =‘COMPLETED’

AND ACTIONTIME > 0

Desired data:


Each time I write the query, I get an error that states:

DB2 Database Error: ERROR [42804] [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.

db2sql database
9kgfl.png (8.1 KiB)
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

·
Kev Riley avatar image
Kev Riley answered

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.

10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.