October 3, 2012 at 2:37 am
Hi
I have this query
SELECT
BUS_FUNC_CLASS,
CFH.POLICY_ID,
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 CONVERT(NUMERIC(20,2), ABS(CFH.VALUE)/100) ELSE 0 END)
WHEN SP.IFA_REMUNE_TYPE = 'FEES' AND SP.CHARGE_TYPE IN ('SIC','ANAC')
THEN CAST(('n/a') AS VARCHAR(16))
WHEN SP.IFA_REMUNE_TYPE = 'FEES' AND SP.CHARGE_TYPE = 'PLATFORM'
THEN CAST(('n/a') AS VARCHAR(16))
END[INITIALFEECRG]
FROM..... then I join my tables.
But then I get this error:
Error converting data type varchar to numeric, which I belive it's here: CAST(('n/a') AS VARCHAR(16)) but I just can't get it right.
Please help.
October 3, 2012 at 2:42 am
The error is converting Varchar to Numeric, so the only conversion of that type happening which is explicit is
CONVERT(NUMERIC(20,2), ABS(CFH.VALUE)/100) ELSE 0 END)
So you either have values in CFH.value which cannot be converted or it is erroring on the sum as that will return a numeric value, so that will need to be converted back to a varchar so that the N/A's can work in the case statement.
October 3, 2012 at 3:05 am
I changed it my code to
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((CFH.VALUE/100)AS VARCHAR(16)) ELSE 0 END)
WHEN SP.IFA_REMUNE_TYPE = 'FEES' AND SP.CHARGE_TYPE IN ('SIC','ANAC')
THEN CAST(convert(varchar(10), 'n/a') AS VARCHAR)
--WHEN SP.IFA_REMUNE_TYPE = 'FEES' AND SP.CHARGE_TYPE = 'PLATFORM'
--THEN CAST(convert(varchar(10), 'n/a') AS VARCHAR)
END[INITIALFEECRG],
And now I get: Conversion failed when converting the varchar value 'n/a' to data type int.
October 3, 2012 at 3:06 am
Because SUM returns an INT value, you need to cast the whole SUM command back to VARCHAR, not just the cfh.value/100
October 3, 2012 at 3:11 am
Thank you
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply