Hi,

I am doing a union between a couple of select statements. The select statement runs fine on it's own, however when i do the union it gives me this error:

 

Here is the code below:

I think the problem I am having is with the joins, but I just cant seem to fix it.

Help would be great!

Brett

I think the problem is that your union has different datatypes
Try running this and you will understand the problem
--fails
select 1
union all
select'abc'

--works
select convert(varchar,1)
union all
select'abc'


basically all the fields in a union have to be of the same datatype

“I sense many useless updates in you... Useless updates lead to fragmentation... Fragmentation leads to downtime...Downtime leads to suffering..Fragmentation is the path to the darkside.. DBCC INDEXDEFRAG and DBCC DBREINDEX are the force...May the force be with you" -- http://sqlservercode.blogspot.com/

I'm pretty sure the datatypes are the same. The problem is that I am not even selecting "app form" (see the error) from the table.

app form is the value in one of the fields

what about running only this
SELECT isnull(d.lk_value, 'No District Captured') AS District, isnull(sd.lk_value, 'No Subdistrict Captured') as subdistrict, COUNT(*) AS 'Total Cancel', a.lk_value AS 'Reason', 382 AS 'document_type'
FROM tfs_reg_application ap
Left JOIN apl_generic_lookup d on d.lk_id = ap.district_code
Left JOIN tfs_town tn on ap.town_id = tn.town_id
Left JOIN apl_generic_lookup sd on tn.subdistrict_id = sd.lk_value
INNER JOIN dbo.tfs_reg_audit_log l ON ap.folder_id = l.folder_id
Left JOIN dbo.apl_generic_lookup a ON l.action_reason = a.lk_id
WHERE (ap.appl_cancelled = 1) AND (l.current_item = 'Y')
GROUP BY a.lk_value, d.lk_value, sd.lk_value


and then only this

select isnull(gl.lk_value, 'No District Captured') as District, isnull(b.lk_value, 'No Subdistrict Captured') as subdistrict, count(*) as 'Total Cancel', a.lk_value as 'Reason', 384 as 'document_type'
from tfs_wf_lc lc
Left JOIN apl_generic_lookup gl on gl.lk_id = lc.district
Left JOIN apl_generic_lookup b on b.lk_id = lc.subdistrict
Inner Join tfs_wf_lc_audit_log la on lc.lc_id = la.lc_id
LEFT Join apl_generic_lookup a on la.cancel_reason = a.lk_id
Where lc_cancelled = 1
and la.current_item = 'Y'
Group By a.lk_value, gl.lk_value, b.lk_value

If that doesn't give an error then that is your problem
If you have an error in one of these 2 then take a look at your joins maybe you are joining different datatypes


“I sense many useless updates in you... Useless updates lead to fragmentation... Fragmentation leads to downtime...Downtime leads to suffering..Fragmentation is the path to the darkside.. DBCC INDEXDEFRAG and DBCC DBREINDEX are the force...May the force be with you" -- http://sqlservercode.blogspot.com/

Like I said, the select statements do run on their own. Its only when I do a union that this error occurs. Also app form is the first row in apl_generic_lookup the thing is that I am not meant to be calling any data from this row.