user6592730 user6592730 - 2 months ago 32
SQL Question

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

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

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?

Comments