Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > SQL Server > SQL Server 2000 > SQL Server 2000
Password Reminder
Register
| FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
SQL Server 2000 General discussion of Microsoft SQL Server -- for topics that don't fit in one of the more specific SQL Server forums. version 2000 only. There's a new forum for SQL Server 2005.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Server 2000 section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old December 21st, 2005, 10:41 AM
Authorized User
 
Join Date: Jun 2005
Location: , , .
Posts: 17
Thanks: 0
Thanked 0 Times in 0 Posts
Default Problems with joins

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:

 
Quote:
quote:Syntax error converting the varchar value 'App Form' to a column of data type int.


Here is the code below:

Code:
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


Union
 
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
I think the problem I am having is with the joins, but I just cant seem to fix it.

Help would be great!

Brett

Reply With Quote
  #2 (permalink)  
Old December 21st, 2005, 10:47 AM
Authorized User
 
Join Date: Sep 2005
Location: , , .
Posts: 95
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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/
Reply With Quote
  #3 (permalink)  
Old December 21st, 2005, 11:02 AM
Authorized User
 
Join Date: Jun 2005
Location: , , .
Posts: 17
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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.

Reply With Quote
  #4 (permalink)  
Old December 21st, 2005, 11:04 AM
Authorized User
 
Join Date: Sep 2005
Location: , , .
Posts: 95
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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/
Reply With Quote
  #5 (permalink)  
Old December 22nd, 2005, 02:50 AM
Authorized User
 
Join Date: Jun 2005
Location: , , .
Posts: 17
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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.

Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
What are joins? Bhalchandra SQL Server 2000 2 July 2nd, 2007 11:29 PM
Joins nalla Oracle 0 December 14th, 2005 05:54 AM
Joins r_ganesh76 SQL Server 2000 2 February 10th, 2005 12:21 AM
Joins marthaj SQL Server 2000 7 June 26th, 2003 09:02 AM



All times are GMT -4. The time now is 11:48 PM.


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.