Order By in a UNION - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Order By in a UNION

Author

Topic

Ozzie19
Starting Member

2 Posts

Posted - 2009-03-04 : 08:59:39

I have a pretty beefy UNION query - right now I get "ORDER BY items must appear in the select list if the statement contains a UNION, INTERSECT or EXCEPT operator." which makes no sense since VACV.Level1OrderBy is in both halves of the UNION.

SELECT VVT.VehicleID, VVT.Name, LVL.[Name], LVL.AssetClassificationID, ACL.Level1Name, ACL.Level1ID, ACL.Level1Ref,
ACL.Level2Name, ACL.Level2ID, ACL.Level2Ref, ACL.Level3Name, ACL.Level3ID, ACL.Level3Ref,
ACL.Level4Name, ACL.Level4ID, ACL.Level4Ref, ACL.Level5Name, ACL.Level5ID, ACL.Level5Ref,
ACL.Level6Name, ACL.Level6ID, ACL.Level6Ref, COALESCE( AV.MinimumInvestmentAmount, VVT.MinimumInvestmentAmount, 0 ),
VP.[1YearReturn], VP.[3YearAnnualizedReturn], VP.[5YearAnnualizedReturn], VP.[60MonthStdDev], VVT.TotalExpenseRatio,
VD.Filename, VD.FileType, 0 AS Selected, 0 AS Favorite,
CASE WHEN @ModelLevel < 6 THEN NULL ELSE VACV.Level6OrderBy END AS Level6OrderBy,
CASE WHEN @ModelLevel < 5 THEN NULL ELSE VACV.Level5OrderBy END AS Level5OrderBy,
CASE WHEN @ModelLevel < 4 THEN NULL ELSE VACV.Level4OrderBy END AS Level4OrderBy,
CASE WHEN @ModelLevel < 3 THEN NULL ELSE VACV.Level3OrderBy END AS Level3OrderBy,
CASE WHEN @ModelLevel < 2 THEN NULL ELSE VACV.Level2OrderBy END AS Level2OrderBy,
VACV.Level1OrderBy AS Level1OrderBy, Risk.RiskMetrics
FROM dbo.ClassificationLevels ACL
INNER JOIN dbo.ModelVehiclesVVT VVT ON ACL.Level1ID = vvt.AssetClassificationID
INNER JOIN dbo.Classifications LVL ON VVT.AllocationAssetClassificationID = LVL.AssetClassificationID
INNER JOIN dbo.Vehicles AV ON VVT.VehicleID = AV.VehicleID AND AV.OrgID = @OrgID
LEFT OUTER JOIN dbo.VehicleTypes VT ON VVT.VehicleID = VT.VehicleID AND VT.OrgID = @EnterpriseOrgID
INNER JOIN dbo.ClassificationLevelsOrderByView VACV ON VVT.AssetClassificationID = VACV.Level1ID
LEFT OUTER JOIN dbo.Documents VD ON VVT.VehicleID = VD.VehicleID
LEFT OUTER JOIN dbo.Performance VP ON VVT.VehicleID = VP.VehicleID
LEFT OUTER JOIN dbo.RiskMetrics Risk ON VVT.VehicleID = Risk.VehicleID AND Risk.ProgramID = @ProgramID
LEFT OUTER JOIN dbo.CompositionVehicles PCV ON VVT.VehicleID = PCV.VehicleID AND PCV.OrgID = @OrgID
WHERE VVT.ProgramID = @ProgramID AND VVT.ModelID = @ModelID AND PCV.VehicleID IS NULL
UNION
INSERT INTO #TempProgramModelVehiclesList(VehicleID, ShortName, AllocationAssetClassificationName, AllocationAssetClassificationID,
Level1Name, Level1ID, Level1Ref,
Level2Name, Level2ID, Level2Ref, Level3Name, Level3ID, Level3Ref, Level4Name, Level4ID, Level4Ref, Level5Name, Level5ID,
Level5Ref, Level6Name, Level6ID, Level6Ref, MinimumInvestmentAmount, [1YearReturn], [3YearAnnualizedReturn],
[5YearAnnualizedReturn], [60MonthStdDev], TotalExpenseRatio, [FileName], FileType, Selected, Favorite,
Level6OrderBy, Level5OrderBy, Level4OrderBy, Level3OrderBy, Level2OrderBy, Level1OrderBy, RiskMetrics)
SELECT VICH.VehicleID, VICH.ShortName, VICH.AssetClassificationName AS [Name], VICH.AllocationAssetClassificationID, VACV.Level1Name,
VACV.Level1ID, VACV.Level1Ref, VACV.Level2Name, VACV.Level2ID, VACV.Level2Ref, VACV.Level3Name, VACV.Level3ID, VACV.Level3Ref,
VACV.Level4Name, VACV.Level4ID, VACV.Level4Ref, VACV.Level5Name, VACV.Level5ID, VACV.Level5Ref, VACV.Level6Name, VACV.Level6ID,
VACV.Level6Ref, VICH.MinimumInvestmentAmount, VICH.[1YearReturn], VICH.[3YearAnnualizedReturn], VICH.[5YearAnnualizedReturn],
VICH.[60MonthStdDev], VICH.TotalExpenseRatio, VD.FileName, VD.FileType, 0, 0,
CASE WHEN @ModelLevel < 6 THEN NULL ELSE VACV.Level6OrderBy END AS Level6OrderBy,
CASE WHEN @ModelLevel < 5 THEN NULL ELSE VACV.Level5OrderBy END AS Level5OrderBy,
CASE WHEN @ModelLevel < 4 THEN NULL ELSE VACV.Level4OrderBy END AS Level4OrderBy,
CASE WHEN @ModelLevel < 3 THEN NULL ELSE VACV.Level3OrderBy END AS Level3OrderBy,
CASE WHEN @ModelLevel < 2 THEN NULL ELSE VACV.Level2OrderBy END AS Level2OrderBy,
VACV.Level1OrderBy AS Level1OrderBy, RiskHV.RiskMetrics
FROM dbo.InCurrentHolding VICH
INNER JOIN dbo.VehicleTypes VVT ON VICH.VehicleID = VVT.VehicleID AND VVT.OrgID = @EnterpriseOrgID
INNER JOIN dbo.ClassificationLevelsOrderByView VACV ON VVT.AssetClassificationID = VACV.Level1ID
LEFT OUTER JOIN dbo.Documents AS VD ON VICH.VehicleID = VD.VehicleID
INNER JOIN RiskMetrics Risk ON RiskHV.VehicleID = VICH.VehicleID
WHERE VICH.ProposalID = @ProposalID
ORDER BY
CASE
WHEN @ModelLevel = 1 THEN (RANK() OVER (ORDER BY VACV.Level1OrderBy ))
END

Buehler?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-03-04 : 09:03:53

your query doesnt make sense. whats the purpose of insert in between union?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources