How to solve "Arithmetic overflow error converting varchar to data type numeric" error in sql server?

Question

My query gets data from two tables and join them to give output as a single output. Subquery works without any error. But when i run the whole query i get an error like this.

Arithmetic overflow error converting varchar to data type numeric.

This is my query

SELECT a.REF_NO + ' - '
       + (SELECT DISTINCT ( c.c_name )
          FROM   C_MASTER c,
                 A_MASTER a
          WHERE  a.c_no = c._NO) AS refer
FROM   [A_MASTER] a,
       [C_MASTER] c
WHERE  a.c_no = c._no
ORDER  BY a.REF_NO   

REF_NO is numeric data type.

Answer 1

Try this:

select CAST(a.REF_NO AS VARCHAR(100))
        +' - '
        +(select distinct (c.c_name) from C_MASTER c, A_MASTER a
            where a.c_no=c._NO) as refer
from [A_MASTER] a, [C_MASTER] c
where a.c_no=c._no
order by a.REF_NO

Overall this looks like a sloppy query to me, but I can't improve upon it without more explanation about what you're trying to do, what you want your results to look like, and what your tables look like. Also, is there a reason you aren't using joins?

Answer 2

Try out with the below query. Use a join instead of separating tables with comma when writing queries.

 Select  distinct  cast(a.ref_no as VARCHAR(50))+'-'+c.c_name 
 from C_MASTER c
      Join A_MASTER a
                        On a.c_no=c._NO