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 ' '
Quote from: ry1633 on 14 Sep 2017 10:41:41 AM
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.
case
when [signature field] = 'signed' AND [disp field] is not null
then [date_field]
else null
end
or
case
when [signature field] = 'signed' AND [disp field] is not null
then [date_field]
else cast ( null, date )
end