Cannot create a order by view

Hello,

I am trying to create a simple view which uses self joins. I want the final result order by. I am able to create the view using Top clause followed by order by but when I simply query the view it does not appear in the correct order. Can some body help me with the issue.

CREATE VIEW [dbo].[vw_SalesRep_Chaining]

AS

SELECT Top 100 percent Rep_id AS RepId,

Rep_cd AS RepCode,

Region as Region,

CASE WHEN Market IN ('Arizona - North', 'Arizona - South', 'Gtr TX / New Mexico') THEN 'Arizona / New Mexico'

WHEN Market IN ('L.A. Metro - West', 'L.A. Metro - North', 'L.A. Metro - East') THEN 'LA Metro'

WHEN Region = 'Western' AND Market NOT IN ('Arizona - North', 'Arizona - South', 'Gtr TX / New Mexico', 'L.A. Metro - West', 'L.A. Metro - North', 'L.A. Metro - East') THEN 'Western - Other'

ELSE '' END AS Sub_Region,

RSM AS RSM,

UPPER(Regional_Manager) AS Regional_Manager,

Market,

Rep_Manager_id AS SalesManager,

UPPER(MarketManager) AS MarketManager,

New_Position AS NewPosition,

Rep_Status AS RepStatus,

UPPER(Rep_Payroll_name) AS PayrollName

FROM (SELECT SalesRep_GUID, Rep_id, Rep_cd, Rep_Payroll_name, Rep_SSN, Rep_Status, Hire_dt, Termination_dt, Commission_start_dt,

Rep_Manager_id, Region, Market, New_Position, Validity_start_dt, Validity_end_dt, Load_interval_id, Create_process_id, current_ind

FROM dbo.SalesRep

WHERE (current_ind = 1) AND (New_Position IN ('SC1', 'SC2', 'SC3', 'MSO')) AND (Region NOT IN ('Emerging Market')) OR

(current_ind = 1) AND (New_Position IS NULL) AND (Region IN ('TeleSales')) AND (Region NOT IN ('Emerging Market'))) AS A

INNER JOIN (SELECT Rep_id RepId, Rep_Payroll_name as MarketManager, Rep_Manager_id as RSM

FROM dbo.SalesRep AS SalesRep_2

WHERE (current_ind = 1)) AS B

ON A.Rep_Manager_id = B.Repid

INNER JOIN (SELECT Rep_id RepId1, Rep_Payroll_name as Regional_Manager FROM dbo.SalesRep AS SalesRep_1

WHERE (current_ind = 1)) AS C

ON B.RSM = C.Repid1

WHERE (A.Region IS NOT NULL)

Order by Rep_Id, Rep_Cd

This is the script I have used to create the view. But when is simply query the view it does not appear in the order of Rep_Id, Rep_Cd




Answer this question

Cannot create a order by view