You are right, that is what is causing the error. The first WHEN block in your case expression yields data type of numeric(20,2). So SQL requires the other WHEN blocks also to yield the same data type or something that can be converted to that data type. Since 'n/a' cannot be converted, the error happens.
Instead of returning 'n/a', you can simply return null and use that in the client code or reporting services (or any other presentation layer that you are using) to display n/a.
If you do want to return 'n/a' from SQL (which most experts would advise against), you will need to convert the first WHEN block also to yield varchar(16).CASE
WHEN SP.IFA_REMUNE_TYPE = 'COMMISSION' AND SP.CHARGE_TYPE IN ('SIC', 'ANAC') THEN
SUM(
CASE
WHEN CASH_FLOW_NAME = 'PR_INITIAL_FEE' THEN CAST(CONVERT(NUMERIC(20, 2), ABS(CFH.VALUE) / 100) AS VARCHAR(16))
ELSE '0'
END
)
WHEN SP.IFA_REMUNE_TYPE = 'FEES' AND SP.CHARGE_TYPE IN ('SIC', 'ANAC') THEN
CAST(('n/a') AS VARCHAR(16))
Error converting data type varchar to numeric
Posted - 10/03/2012 : 04:39:56
