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.I

Solution 1:

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 innerjoin Job on Ad.Id = Job.AdId
     Where (select JobTitle from JobTitle where JobTitle.Id = Job.TitleId)Like@titleor@titleisnullOrderbycaseWhen@sortCol='PublishDate'and@sortDir='ASC'Then Ad.PublishDate EndASC,
 caseWhen@sortCol='PublishDate'and@sortDir='DESC'Then Ad.PublishDate EndDESC,
 caseWhen@sortCol='LastDate'and@sortDir='ASC'Then Ad.LastDate EndASC,
 caseWhen@sortCol='LastDate'and@sortDir='DESC'Then Ad.LastDate EndDESC

So in that case I'm using View ..

Solution 2:

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 )

Solution 3:

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