Aliasing Problem

  • paras_98

    SSCrazy

    Points: 2884

    Can i use a alias name in a "where" clause to filter records.

    eg.

    select employeename en, age from employees

    where en like 'a%'

    i am getting an error 'en' column not found.

    how do i get over this problem.

    Paras Shah

    Evision Technologies

    Mumbai, India


    Paras Shah
    Evision Technologies
    Mumbai, India

  • jwiner

    SSCrazy

    Points: 2241

    try wrapping your alias in single quotes

    i.e.

    select sfirstname 'en', slastname from tbl_applicants

    where 'en' like 'a%'

  • Andy Warren

    SSC Guru

    Points: 119684

    I don't think that will work either. Why would you care? The reason for the alias is to "pretty" it up for display, especially if you're using a function or concatening two values where the result would not have a column name.

    Andy

  • paras_98

    SSCrazy

    Points: 2884

    ya ... single quotes did solve my problem.

    Paras Shah

    Evision Technologies

    Mumbai, India


    Paras Shah
    Evision Technologies
    Mumbai, India

  • schmitzericj

    Newbie

    Points: 9

    It's correct from a syntax point of view, but in your where statement, you are comparing the string 'en' with 'a%', not with the employeename.

    I hope this feature will be implemented in the next version of SQL Server. In fact, it's very annoying to make a calculation and not being able to reused it elsewhere in the select...


    Eric

  • GRN

    Hall of Fame

    Points: 3260

    the whole idea of using a aliased column in the where clause goes against the way the query is processed in SQL . the where clause would be resolved first and then the computations for the select list .. so i would not keep any hopes for this 'feature' in later versions!!why don't you use a derived table instead? it may be over kill but it would certainly work!

Viewing 6 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply