union query code from MS Access to SQL

[44nato44]

Hi

I am converting my asp code so I can use it together with a SQL server.

I have the following union query which use to work with my MS Access DB but does not work with my SQL database.

Any ideas ?

SELECT ProjectID as ProjectName FROM tblTaskToolProRght WHERE [UserID] = 'nato' AND [SuperUser] = 1 UNION SELECT ProjectName FROM tblProjects WHERE SponsorID = 3;

Thanks

 

[wvdba]

you have to qualify ProjectName
like THIS:

SELECT ProjectID as ProjectName FROM tblTaskToolProRght TTP WHERE [UserID] = 'nato' AND [SuperUser] = 1 
UNION 
SELECT PJ.ProjectName FROM tblProjects PJ WHERE SponsorID = 3;

to avoid ambiguity.

 

[gmmastros]

I don't think the solution is to qualify your table names (using an alias). If I had to guess, I would say there is a data type mismatch problem. You see, when you write a union query, there are a couple things you need to be careful of. Each query must return the same number of columns, and the data type of those columns *SHOULD* be the same. If the data types are not the same, then SQL will attempt to convert them for you. The problem with letting SQL do the data type conversions for you is... their rules may not be what you expect them to be.

Here is a simple query that demonstrates the problem:

Select 1, 'First Query'

Union

Select [!]'[/!]2[!]'[/!], 'Second Query'

Copy/paste the code above in to a SQL Server query window and run it. It runs just fine without any errors. Notice that the second query has single-quotes around the '2'. The single quotes identify this as a string. Since we have a UNION query, SQL will look at the data types (for each column) and decide what the resultant data type should be. For the first column, it sees 1 & '2'. SQL will interpret this as an integer and a string. Since integers have a higher precedence than strings, SQL will convert all the data in the first column to an int.

Now look at this query:

Select 1, 'First Query'

Union

Select '2', 'Second Query'

Union

Select '[!]three[/!]', 'Third Query'

Notice the first column. We have 1, '2' & 'three'. Again, SQL will convert this to an int, but 'three' cannot be converted so you get an error instead.

To correct this problem, we can force the conversion to our liking, like this:

Select [!]Convert(VarChar(20),[/!] 1[!])[/!], 'First Query'

Union

Select '2', 'Second Query'

Union

Select 'three', 'Third Query'

Now you can see that I am forcing the data in the first column of the first query to be a string. Since the other queries also contain strings, the output result will have a string column, and there are no conversion errors.

Now... I'm not saying this is the problem you are getting, but it is my best guess. If this does not fix your problem, I encourage you to copy/paste your query to a SQL Server query window and run it. If you still get an error message, then post it here.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[44nato44]

Thanks a lot for your help, but I used Union All and removed the first order by and that took care of my problem.

Thanks again

 

[gmmastros]

There wasn't any order by's in your original post.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[foxbox]

I expect a ProjectID to be numeric and a Projectname varchar. So in this query you are combining 2 SELECT's that do not produce the same field TYPE (= George's remark):

SELECT ProjectID as ProjectName 
FROM tblTaskToolProRght 
WHERE [UserID] = 'nato' AND [SuperUser] = 1 

UNION 
SELECT ProjectName 
FROM tblProjects 
WHERE SponsorID = 3;

So i bet the error had nothing to do with UNION ALL and/or ORDER BY.