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

2
votes

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.

2
sqlsql-servertsql
Show example data and desired results. - Martin Smith
result should be like this => 10001 - annual competition - Mike

2 Answers

3
votes

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?

1
votes

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