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.
SELECT a.REF_NO + ' - '
+ (SELECT DISTINCT ( c.c_name )
FROM C_MASTER c,
WHERE a.c_no = c._NO) AS refer
FROM [A_MASTER] a,
WHERE a.c_no = c._no
ORDER BY a.REF_NO
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?