Case Statement

Epicor ERP 10
1

Hello All,

Anyone able to help me with this case statement?

(CASE WHEN 
    UD100.DateCustContact_c = NULL THEN UD100.CollectionDate_c ELSE
 (CASE WHEN UD100.CollectionDate_c = NULL THEN UD100.AvailableDate_c ELSE 
 (CASE WHEN UD100.AvailableDate_c = NULL THEN CAST('UD100.EstimatedDateMth_c' AS varchar) ELSE
 (CASE WHEN CAST('UD100.EstimatedDateMth_c' AS VARCHAR) = NULL THEN 'No data' ELSE CAST('UD100.EstimatedDateMth_c' AS varchar) end) end) end) end)

image
image920×600 36.2 KB

The other fields are date fields and EstimatedDateMth_c is a string field.

The way I would like it to work and tried to achieve here is fall back date 4,3,2,1 but one of them is a string field.

Kind regards,
Aaron.

2

If anyone else running into this issue with help from @hkeric.wci I was able to resolve this with the following statement

COALESCE(CONVERT(nvarchar, UD100.DateCustContact_c, 105), CONVERT(nvarchar, UD100.CollectionDate_c, 105), CONVERT(nvarchar, UD100.AvailableDate_c, 105), UD100.EstimatedDateMth_c)

Field Type: nvarchar

1 Like