Professional Web Applications Themes

use an aliased column in a WHERE clause? - Microsoft SQL / MS SQL Server

i could swear i used to do this kind of thing in M$Access all the time: SELECT NameLast + ', ' + NameFirst AS NameFull FROM employees WHERE NameFull = 'Smith, Joe' but SQL Server keeps telling me 'NameFull is an invalid column name'. is there some bit of syntax i am missing, or is this just impossible to do? TIA! =)...

  1. #1

    Default use an aliased column in a WHERE clause?

    i could swear i used to do this kind of thing in M$Access all the time:

    SELECT NameLast + ', ' + NameFirst AS NameFull FROM employees WHERE NameFull
    = 'Smith, Joe'

    but SQL Server keeps telling me 'NameFull is an invalid column name'.

    is there some bit of syntax i am missing, or is this just impossible to do?

    TIA! =)


    K. Guest

  2. #2

    Default Re: use an aliased column in a WHERE clause?

    correct syntax would be

    SELECT NameLast + ', ' + NameFirst AS NameFull FROM employees WHERE NameLast
    + ', ' + NameFirst = 'Smith, Joe'

    OR

    select * from
    (SELECT NameLast + ', ' + NameFirst AS NameFull FROM employees) a
    WHERE NameFull = 'Smith, Joe'

    -Vishal

    "K. Shier" <> wrote in message
    news:phx.gbl... 
    NameFull 
    do? 


    Vishal Guest

  3. #3

    Default Re: use an aliased column in a WHERE clause?

    SELECT * FROM
    (SELECT NameLast + ', ' + NameFirst AS NameFull
    FROM employees) AS X
    WHERE NameFull = 'Smith, Joe'

    --
    David Portas
    ------------
    Please reply only to the newsgroup
    --


    David Guest

  4. #4

    Default Re: use an aliased column in a WHERE clause?

    That is because Access is a file based data system masquerading as a SQL
    database. Columns do have an order in Access, where in a proper SQL database
    like SQL Server they are unordered.

    You have to use something like:
    SELECT NameLast + ', ' + NameFirst AS NameFull FROM employees WHERE NameLast
    = 'Smith' AND NameFirst = 'Joe'
    in SQL Server.

    Here is a explanation from Joe Celko, note that the where clause is
    processed before the select:
    "
    Here is how a SELECT works in SQL ... at least in theory. Real products
    will optimize things when they can.

    a) Start in the FROM clause and build a working table from all of the
    joins, unions, intersections, and whatever other table constructors are
    there. The table expression> AS <correlation name> option allows you
    give a name to this working table which you then have to use for the
    rest of the containing query.

    b) Go to the WHERE clause and remove rows that do not pass criteria;
    that is, that do not test to TRUE (reject UNKNOWN and FALSE). The WHERE
    clause is applied to the working in the FROM clause.

    c) Go to the optional GROUP BY clause, make groups and reduce each
    group to a single row, replacing the original working table with the new
    grouped table. The rows of a grouped table must be group
    characteristics: (1) a grouping column (2) a statistic about the group
    (i.e. aggregate functions) (3) a function or (4) an expression made up
    of the those three items.

    d) Go to the optional HAVING clause and apply it against the grouped
    working table; if there was no GROUP BY clause, treat the entire table
    as one group.

    e) Go to the SELECT clause and construct the expressions in the list.
    This means that the scalar subqueries, function calls and expressions in
    the SELECT are done after all the other clauses are done. The AS
    operator can give a name to expressions in the SELECT list, too. These
    new names come into existence all at once, but after the WHERE clause,
    GROUP BY clause and HAVING clause has been executed; you cannot use them
    in the SELECT list or the WHERE clause for that reason.

    If there is a SELECT DISTINCT, then redundant duplicate rows are
    removed. For purposes of defining a duplicate row, NULLs are treated as
    matching (just like in the GROUP BY).

    f) Nested query expressions follow the usual scoping rules you would
    expect from a block structured language like C, Pascal, Algol, etc.
    Namely, the innermost queries can reference columns and tables in the
    queries in which they are contained.
    "
    --
    Jacco Schalkwijk MCDBA, MCSD, MCSE
    Database Administrator
    Eurostop Ltd.


    "K. Shier" <> wrote in message
    news:phx.gbl... 
    NameFull 
    do? 


    Jacco Guest

Similar Threads

  1. Replies: 0
    Last Post: February 24th, 01:06 PM
  2. Replies: 3
    Last Post: October 27th, 11:24 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139