CODEWITHSUNDEEP
sqlsql-servert-sql
jh86
jh86

Reputation: 39

Need help creating a view

CREATE VIEW custowner
AS
   (SELECT        
        RTRIM(p.FirstName)+ ' ' + p.LastName AS [name], 
        LEFT(p.Phone, 12) AS phone, 
        p.CellPhone AS cellphone, 
        LEFT(p.EMail, 22) AS email
    FROM  
        CUSTOMER_OWNER co 
    INNER JOIN 
        PEOPLE p ON co.PeopleID = p.PeopleId
    ORDER BY 
        name ASC);

I get an error:

The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP, OFFSET or FOR XML is also specified.

How can I fix this?

Upvotes: 0

Views: 57

Answers (2)

Harshil Doshi
Harshil Doshi

Reputation: 3592

The solution here is to remove order by clause from your query. If you want to display results in particular order of name, you can do that while selecting data from view.

Upvotes: 3

Ruslan Tolkachev
Ruslan Tolkachev

Reputation: 644

There is a way to do it, but you should probably follow advise to order it on a view. However to hack it you can use TOP 100 PERCENT in your SELECT statement

create view custowner
as
(SELECT TOP 100 PERCENT     
rtrim(p.FirstName)+ ' ' + p.LastName as [name], 
left(p.Phone, 12) as phone, 
p.CellPhone as cellphone, 
left(p.EMail, 22) as email
FROM CUSTOMER_OWNER co INNER JOIN PEOPLE p
ON co.PeopleID = p.PeopleId
order by name ASC);

It should work now

Upvotes: 0

Related Questions