Using SQL VIEW with WHERE Clause (ERROR)

Question

I have 3 tables: Entry, User, Comment. I am showing the comment count and user (sender) full name for each entry by an sql view. But when I am using a where clause it's giving an error.

My SQL View is like:

CREATE VIEW [dbo].Entry_View
    AS SELECT 
            E.Id AS [Id], 
            Convert(varchar(10), E.Date, 104) AS [Date], 
            E.Subject AS [Subject], 
            E.Content AS [Content], 
            E.Faculty AS [Faculty], 
            E.Category AS [Category],
            (U.Firstname + ' ' + U.Lastname) AS [User],
            E.Department AS [Department],
            E.ViewCount AS [View],
            E.SupportCount AS [Support],
            Count(C.Entry_Id) AS [Comment] 
        FROM (Entry E INNER JOIN User U ON U.Id = E.User_Id)
        LEFT JOIN Comment C on C.Entry_Id=E.Id 
        GROUP BY
            E.Id, E.Date, E.Subject, E.Content, 
            E.Faculty, E.Category, (U.Firstname + ' ' + U.Lastname), 
            E.Department, E.ViewCont, E.SupportCount

When I am calling: SELECT * FROM Entry_View WHERE E.Department = 'Administration'

I am getting an error: The multi-part identifier "E.Department" could not be bound.

I couldn't solve this problem and need help. Thanks.

It's not a duplicate of that thread. That thread does not include a view in the problem definition. — Dan Bracuk, Dec 24 '13 at 19:06
@DanBracuk The view has absolutely nothing to do with this question though. — Zane, Dec 24 '13 at 19:07
I think it's not duplicate. There are lots of `The multi-part identifier could not be bound` questions, but not exact solutions for my question. — smtnkc, Dec 24 '13 at 19:18
Just a wild guess, but maybe the problem is that "The multi-part identifier "E.Department" could not be bound.". — , Dec 25 '13 at 13:39

score 2 · Answer 1 · answered Dec 24 '13 at 19:02

The aliases used in the view definition are not available when queryting the view. So this:

SELECT * FROM Entry_View WHERE E.Department = 'Administration'

has to become this:

SELECT * FROM Entry_View E WHERE E.Department = 'Administration'

or this:

SELECT * FROM Entry_View WHERE Department = 'Administration'

score 1 · Accepted Answer · answered Dec 24 '13 at 19:01

1

You select E.Department AS Department....so

SELECT * FROM Entry_View WHERE Department = 'Administration'

answered Dec 24 '13 at 19:01

Digital Chris

6,177
1
20
29

score 1 · Answer 3 · answered Dec 24 '13 at 19:01

1

You may try: SELECT * FROM Entry_View WHERE Department = 'Administration'

answered Dec 24 '13 at 19:01

MillaresRoo

3,808
1
31
37

Using SQL VIEW with WHERE Clause (ERROR)

3 Answers3