UNION generates error : The column prefix 'C' does not match with a table name or alias name used in the query.

I have the following two SQL Statements, each returns five columns of the same type and, by themselves, each runs perfectly OK.

SELECT GetDate() as [Date], 'Category' as [Type], '' as [Field],
CATEGORIES.CATEGORYCODE as [Code], COUNT(CONTACTS.CATEGORYCODE) as [Count]
FROM CATEGORIES LEFT OUTER JOIN CONTACTS
ON CONTACTS.CATEGORYCODE=CATEGORIES.CATEGORYCODE
GROUP BY CATEGORIES.CATEGORYCODE

SELECT GetDate() as [Date], 'Attribute' as [Type], ST R(C.ATTRIBUTETYPEID) as [Field],
C.ATTRIBUTECODE as [Code], COUNT(A.CONTACTID) AS [Count]
FROM ATTRIBUTECODES C LEFT OUTER JOIN ATTRIBUTES A
ON C.ATTRIBUTETYPEID = A.ATTRIBUTETYPEID
AND C.ATTRIBUTECODE = A.ATTRIBUTECODE
GROUP BY C.ATTRIBUTETYPEID, C.ATTRIBUTECODE
ORDER BY C.ATTRIBUTETYPEID, C.ATTRIBUTECODE

However, when I insert a UNION ALL clause between the two SELECT statements, I get the following error:
The column prefix 'C' does not match with a table name or alias name used in the query.

What's it going on about?

URGENT : 500 POINTS



ccravenbartleAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

mcmonapCommented:
You have a space between "s tr":

SELECT GetDate() as [Date], 'Attribute' as [Type], ST R(C.ATTRIBUTETYPEID) as [Field],
ccravenbartleAuthor Commented:
I have just discovered that if I remove the ORDER BY clause, the UNION SELECT statements run OK.  However, the problem is that I do need to sort the data in the second SELECT STATEMENT by C.ATTRIBUTETYPEID then by C.ATTRIBUTECODE.

Any ideas?
Mr_PeerapolCommented:
SELECT GetDate() as [Date], 'Category' as [Type], '' as [Field],
CATEGORIES.CATEGORYCODE as [Code], COUNT(CONTACTS.CATEGORYCODE) as [Count]
FROM CATEGORIES LEFT OUTER JOIN CONTACTS
ON CONTACTS.CATEGORYCODE=CATEGORIES.CATEGORYCODE
GROUP BY CATEGORIES.CATEGORYCODE

UNION ALL

SELECT TOP 100 PERCENT GetDate() as [Date], 'Attribute' as [Type], STR(C.ATTRIBUTETYPEID) as [Field],
C.ATTRIBUTECODE as [Code], COUNT(A.CONTACTID) AS [Count]
FROM ATTRIBUTECODES C LEFT OUTER JOIN ATTRIBUTES A
ON C.ATTRIBUTETYPEID = A.ATTRIBUTETYPEID
AND C.ATTRIBUTECODE = A.ATTRIBUTECODE
GROUP BY C.ATTRIBUTETYPEID, C.ATTRIBUTECODE
ORDER BY C.ATTRIBUTETYPEID, C.ATTRIBUTECODE
Fundamentals of JavaScript

Learn the fundamentals of the popular programming language JavaScript so that you can explore the realm of web development.

ccravenbartleAuthor Commented:
The space between the "s tr" is just a typo when I wrote the question. The SQL SELECT statement that I run does not have any typos.
Mr_PeerapolCommented:
Records from your first SELECT will come first. Then sorted records from 2nd SELECT will follow.
Rajesh DalmiaCommented:
Using ORDER By you can't use UNION. only way is that 1st create a view using the ORDER BY clause then use that view in the union.


rdonline1
ccravenbartleAuthor Commented:
Mr Peerapol : I still get the same error using your SELECT TOP 100 PERCENT version.
Mr_PeerapolCommented:
SELECT GetDate() as [Date], 'Category' as [Type], '' as [Field],
CATEGORIES.CATEGORYCODE as [Code], COUNT(CONTACTS.CATEGORYCODE) as [Count]
FROM CATEGORIES LEFT OUTER JOIN CONTACTS
ON CONTACTS.CATEGORYCODE=CATEGORIES.CATEGORYCODE
GROUP BY CATEGORIES.CATEGORYCODE

UNION ALL

SELECT * FROM (
SELECT TOP 100 PERCENT GetDate() as [Date], 'Attribute' as [Type], STR(C.ATTRIBUTETYPEID) as [Field],
C.ATTRIBUTECODE as [Code], COUNT(A.CONTACTID) AS [Count]
FROM ATTRIBUTECODES C LEFT OUTER JOIN ATTRIBUTES A
ON C.ATTRIBUTETYPEID = A.ATTRIBUTETYPEID
AND C.ATTRIBUTECODE = A.ATTRIBUTECODE
GROUP BY C.ATTRIBUTETYPEID, C.ATTRIBUTECODE
ORDER BY C.ATTRIBUTETYPEID, C.ATTRIBUTECODE ) x
ccravenbartleAuthor Commented:
rdonline1: I don't know how to user views in that way.  Please could you edit my SQL Statements to incorporate the view approach and submit them as your answer.
Thanks
ccravenbartleAuthor Commented:
Mr Peerapol:

When I run your modified SELECT statement

SELECT GetDate() as [Date], 'Category' as [Type], '' as [Field],
CATEGORIES.CATEGORYCODE as [Code], COUNT(CONTACTS.CATEGORYCODE) as [Count]
FROM CATEGORIES LEFT OUTER JOIN CONTACTS
ON CONTACTS.CATEGORYCODE=CATEGORIES.CATEGORYCODE
GROUP BY CATEGORIES.CATEGORYCODE
UNION ALL
SELECT * FROM (
SELECT TOP 100 PERCENT GetDate() as [Date], 'Attribute' as [Type], STR(C.ATTRIBUTETYPEID) as [Field],
C.ATTRIBUTECODE as [Code], COUNT(A.CONTACTID) AS [Count]
FROM ATTRIBUTECODES C LEFT OUTER JOIN ATTRIBUTES A
ON C.ATTRIBUTETYPEID = A.ATTRIBUTETYPEID
AND C.ATTRIBUTECODE = A.ATTRIBUTECODE
GROUP BY C.ATTRIBUTETYPEID, C.ATTRIBUTECODE
ORDER BY C.ATTRIBUTETYPEID, C.ATTRIBUTECODE ) x

I get the following error:
ORDER BY items must appear in the select list if the statement contains a UNION operator.
Rajesh DalmiaCommented:
Create View VIEWNAME AS

SELECT GetDate() as [Date], 'Attribute' as [Type], ST R(C.ATTRIBUTETYPEID) as [Field],
C.ATTRIBUTECODE as [Code], COUNT(A.CONTACTID) AS [Count]
FROM ATTRIBUTECODES C LEFT OUTER JOIN ATTRIBUTES A
ON C.ATTRIBUTETYPEID = A.ATTRIBUTETYPEID
AND C.ATTRIBUTECODE = A.ATTRIBUTECODE
GROUP BY C.ATTRIBUTETYPEID, C.ATTRIBUTECODE
ORDER BY C.ATTRIBUTETYPEID, C.ATTRIBUTECODE


*******************

SELECT GetDate() as [Date], 'Category' as [Type], '' as [Field],
CATEGORIES.CATEGORYCODE as [Code], COUNT(CONTACTS.CATEGORYCODE) as [Count]
FROM CATEGORIES LEFT OUTER JOIN CONTACTS
ON CONTACTS.CATEGORYCODE=CATEGORIES.CATEGORYCODE
GROUP BY CATEGORIES.CATEGORYCODE

UNION ALL

Select * from VIEWNAME


ccravenbartleAuthor Commented:
rdonline1:

When I parse the Query:

Create View VIEWNAME AS

SELECT GetDate() as [Date], 'Attribute' as [Type], STR(C.ATTRIBUTETYPEID) as [Field],
C.ATTRIBUTECODE as [Code], COUNT(A.CONTACTID) AS [Count]
FROM ATTRIBUTECODES C LEFT OUTER JOIN ATTRIBUTES A
ON C.ATTRIBUTETYPEID = A.ATTRIBUTETYPEID
AND C.ATTRIBUTECODE = A.ATTRIBUTECODE
GROUP BY C.ATTRIBUTETYPEID, C.ATTRIBUTECODE
ORDER BY C.ATTRIBUTETYPEID, C.ATTRIBUTECODE

I get the following error: The ORDER BY clause is invalid in views, inline functions, derived tables, and subqueries, unless TOP is also specified.

So I modified it to include  a 'TOP 100 PERCENT' clause as in Mr Peerapol's reply above but I then get an 'Incorrect Syntax near the keyword SELECT' error.


Rajesh DalmiaCommented:
1st u have to execute the Create View statement in query analyzer which will create the view and then you have to run the UNION ALL statement.


rdonline1
Rajesh DalmiaCommented:
can u show me the table strcture of ATTRIBUTECODES and ATTRIBUTES table.


rdonline1

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
ccravenbartleAuthor Commented:
Ahh... it works now.

Thanks for your help.  
ccravenbartleAuthor Commented:
TABLE STRUCTURES

Attributes:
ContactID  nvarchar 50
AttributeCode nvarchar 50
AttributeTypeID  int 4
[Value] nvarchar 50
(Key is first 3 fields)

AttributeCodes:
AttributeCode nvarchar 50
AttributeTypeID  int 4
Description  nvarchar 50
(Key is first 2 fields)
Rajesh DalmiaCommented:
as u said it's working now then you should accept the answer


rdonline1
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.