union all on 2 select statements -- see error message - please help

[Mitchell_Collen via AccessMonster.com]

Hi, I want to use a union funtion in a query/stored procedure. Below is the
error message displayed. Please help me to figure out what I am doing so
wrong. Thanks MC


Error: Order by items must appear in the select list if the statement
contains a union operator.

ALTER PROCEDURE Pharmacy.StoredProcedure5
AS SELECT Pharmacy.VIEW_ALL_TECHS.ShortName AS ALLTECHSHORTNAME
FROM Pharmacy.VIEW_ALL_TECHS
UNION ALL
SELECT Pharmacy.VIEW_ALL_PHARMACIST.Title AS ALLPHARMTITLE
FROM Pharmacy.VIEW_ALL_PHARMACIST

 

[Jerry Whittle]

For a good old union query, it should just be:

SELECT Pharmacy.VIEW_ALL_TECHS.ShortName AS ALLTECHSHORTNAME
FROM Pharmacy.VIEW_ALL_TECHS
UNION ALL
SELECT Pharmacy.VIEW_ALL_PHARMACIST.Title AS ALLPHARMTITLE
FROM Pharmacy.VIEW_ALL_PHARMACIST ;

Is this an Oracle or SQL database by chance? Access doesn't store views nor
can you create procedures like you are trying.

 

[Mitchell_Collen via AccessMonster.com]

For a good old union query, it should just be:

SELECT Pharmacy.VIEW_ALL_TECHS.ShortName AS ALLTECHSHORTNAME
FROM Pharmacy.VIEW_ALL_TECHS
UNION ALL
SELECT Pharmacy.VIEW_ALL_PHARMACIST.Title AS ALLPHARMTITLE
FROM Pharmacy.VIEW_ALL_PHARMACIST ;

Is this an Oracle or SQL database by chance? Access doesn't store views nor
can you create procedures like you are trying.

Hi, I want to use a union funtion in a query/stored procedure. Below is the
error message displayed. Please help me to figure out what I am doing so

[quoted text clipped - 9 lines]

SELECT Pharmacy.VIEW_ALL_PHARMACIST.Title AS ALLPHARMTITLE
FROM Pharmacy.VIEW_ALL_PHARMACIST

This is a SQL database with Access 2002
I am trying to use the above SP as a guide for a more complex SP.

This more complex SP will be a funtion or storeprocedure on two separate
views. The funtion will input a between start and end date from the user and
then return a record set containing counts of orders for view X that is
joined one way and view Y that is joined another way. It is important that I
get both counts from each. I figured maybe a Union would work. I am really
stumped. Please advise.