SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Error converting data type varchar to numeric
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

stahorse
Yak Posting Veteran

85 Posts

Posted - 10/03/2012 :  04:39:56  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 10/03/2012 :  06:44:44  Show Profile  Reply with Quote
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))
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.22 seconds. Powered By: Snitz Forums 2000