May 10, 2005 at 11:49 am
I have several views that I want to union together. I have provided an example of two below. What I want to do is a Compute on them. Is this even possible with a UNION query? I have no problem running the UNION with the ORDER BY, but as soon as I add the COMPUTE I get the following error:
ORDER BY items must appear in the select list if the statement contains a UNION operator.
SELECT opvw_n.Team,
opvw_n.Consultant,
opvw_n.[Opportunity Type],
opvw_n.[Opportuntiy Create Date],
opvw_n.Milestone,
opvw_n.[Milestone Start Date],
opvw_n.[Projected Revenue],
opvw_n.[Projected Milestone Completion Date],
opvw_n.[Group ID],
opvw_n.Organizaiton,
opvw_n.[Group],
opvw_n.[Opportunity Name],
opvw_n.[Project Name]
FROM opvw_projections_openNonProj opvw_n
UNION
SELECT opvw_p.Team,
opvw_p.Consultant,
opvw_p.[Opportunity Type],
opvw_p.[Opportuntiy Create Date],
opvw_p.Milestone,
opvw_p.[Milestone Start Date],
opvw_p.[Projected Revenue],
opvw_p.[Projected Milestone Completion Date],
opvw_p.[Group ID],
opvw_p.Organizaiton,
opvw_p.[Group],
opvw_p.[Opportunity Name],
opvw_p.[Project Name]
FROM opvw_projections_openProj opvw_p
ORDER BY Team, [Opportunity Type], Milestone
COMPUTE SUM([Projected Revenue])
BY Team, [Opportunity Type]
COMPUTE SUM([Projected Revenue])
BY Team
May 10, 2005 at 11:59 am
have you tried :
SELECT opvw_p.Team,
opvw_p.Consultant,
opvw_p.[Opportunity Type],
opvw_p.[Opportuntiy Create Date],
opvw_p.Milestone,
opvw_p.[Milestone Start Date],
opvw_p.[Projected Revenue],
opvw_p.[Projected Milestone Completion Date],
opvw_p.[Group ID],
opvw_p.Organizaiton,
opvw_p.[Group],
opvw_p.[Opportunity Name],
opvw_p.[Project Name]
FROM opvw_projections_openProj opvw_p
COMPUTE SUM([Projected Revenue])
BY Team, [Opportunity Type]
COMPUTE SUM([Projected Revenue])
BY Team
ORDER BY Team, [Opportunity Type], Milestone
May 10, 2005 at 1:01 pm
Hadn't thought of that but when I ran it I received the following message:
A COMPUTE BY item was not found in the order by list. All expressions in the compute by list must also be present in the order by list.
At first thought it was going to work because it thought for a little bit.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply