Error converting data type varchar to numeric

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Error converting data type varchar to numeric

Author

Topic

stahorse
Yak Posting Veteran

86 Posts

Posted - 2012-10-03 : 04:39:56

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.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-10-03 : 06:44:44

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))

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources