coure2011 coure2011 - 1 month ago 8
SQL Question

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 @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

bAN bAN
Answer

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

Take a look at THIS

So in that case I'm using View as seen in the article..