Invalid Column name

SELECT Ad.Id, Newspaper, 
    (select Organization from JobOrganization where JobOrganization.Id = Ad.OrganizationId) as Organization,
    Ad.PublishDate, Ad.LastDate,Ad.Url, Job.Id as JobId,
    (select JobTitle from JobTitle where JobTitle.Id = Job.TitleId) as JobTitle1,
    QualificationId, ExpInYears, CategoryId
     FROM Ad inner join Job on Ad.Id = Job.AdId
     Where JobTitle1 Like @title or @title is null
     Order by
 case When @sortCol='PublishDate' and开发者_Python百科 @sortDir='ASC' Then Ad.PublishDate End ASC,
 case When @sortCol='PublishDate' and @sortDir='DESC' Then Ad.PublishDate End DESC,
 case When @sortCol='LastDate' and @sortDir='ASC' Then Ad.LastDate End ASC,
 case When @sortCol='LastDate' and @sortDir='DESC' Then Ad.LastDate End DESC

Error: Invalid column name 'JobTitle1'.

I am using SQL-2008

I'm afraid that you can't use aliases in where clauses,

    SELECT Ad.Id, Newspaper, 
    (select Organization from JobOrganization where JobOrganization.Id = Ad.OrganizationId) as Organization,
    Ad.PublishDate, Ad.LastDate,Ad.Url, Job.Id as JobId,
    (select JobTitle from JobTitle where JobTitle.Id = Job.TitleId) as JobTitle1,
    QualificationId, ExpInYears, CategoryId
     FROM Ad inner join Job on Ad.Id = Job.AdId
     Where (select JobTitle from JobTitle where JobTitle.Id = Job.TitleId)Like @title or @title is null
     Order by
 case When @sortCol='PublishDate' and @sortDir='ASC' Then Ad.PublishDate End ASC,
 case When @sortCol='PublishDate' and @sortDir='DESC' Then Ad.PublishDate End DESC,
 case When @sortCol='LastDate' and @sortDir='ASC' Then Ad.LastDate End ASC,
 case When @sortCol='LastDate' and @sortDir='DESC' Then Ad.LastDate End DESC

So in that case I'm using View ..

JobTitle1 reference name scope is missing here ( Where JobTitle1 Like @title or @title is null ) , try like this ( Where Ad.JobTitle1 Like @title or @title is null )

You cannot use aliases in the WHERE clause because the WHERE clause is evaluated before the SELECT clause.