show date based on two conditions of varying data types

[ry1633]

I need to write an expression that shows a date field if two conditions are met and the two conditions are of different varying types.  I want the date to show if a signature type field is equal to 'signed' and another field of disposition is not null (can be anything just not empty.   signature field and disposition are both VARCHAR2 (50 and 100 bytes, respectively), and the date is type DATE.
I want the date to not show if either condition is not met.  Not sure if should use if-else or case-when.   (The following seems to throw a datatype error - expecting DATE but got CHAR.  )

IF
([signature field]= 'signed'
AND
[disp field] is not null )
THEN [date_field]
ELSE
disp field is null
THEN ' '

[Lynn]

I need to write an expression that shows a date field if two conditions are met and the two conditions are of different varying types.  I want the date to show if a signature type field is equal to 'signed' and another field of disposition is not null (can be anything just not empty.   signature field and disposition are both VARCHAR2 (50 and 100 bytes, respectively), and the date is type DATE.
I want the date to not show if either condition is not met.  Not sure if should use if-else or case-when.   (The following seems to throw a datatype error - expecting DATE but got CHAR.  )

IF
([signature field]= 'signed'
AND
[disp field] is not null )
THEN [date_field]
ELSE
disp field is null
THEN ' '

All possible outcomes from an "if" or "case" must resolve to the same data type so it is not possible to have it return either a date in one instance or a string in another. That is what the error message is telling you.

You could try returning a date or null.

Code Select Expand


case
  when [signature field] = 'signed' AND [disp field] is not null
      then [date_field]
   else null
end


or

Code Select Expand


case
  when [signature field] = 'signed' AND [disp field] is not null
      then [date_field]
   else cast ( null, date )
end