AS alias in where or order clausole 
Author Message
 AS alias in where or order clausole
Why I can use an alias name in the where statement of a query?
Something link this:

select firstname + ' ' + lastname as fullname where fullname='john smith'

or

select firstname + ' ' + lastname as fullname order by fullname

Thanks.

--
Stefano Tempesta



Fri, 04 Apr 2003 03:00:00 GMT
 AS alias in where or order clausole

You can specify an alias in the ORDER BY clause but not in the WHERE clause.
However you can repeat the expression in the WHERE clause to achieve the
desired result:

SELECT firstname + ' ' + lastname AS fullname
FROM tablename
WHERE firstname + ' ' + lastname = 'john smith'
ORDER BY fullname

Hope this helps


Quote:
> Why I can use an alias name in the where statement of a query?
> Something link this:

> select firstname + ' ' + lastname as fullname where fullname='john smith'

> or

> select firstname + ' ' + lastname as fullname order by fullname

> Thanks.

> --
> Stefano Tempesta




Fri, 04 Apr 2003 03:00:00 GMT
 AS alias in where or order clausole

Quote:
>You can specify an alias in the ORDER BY clause but not in the WHERE clause.
>However you can repeat the expression in the WHERE clause to achieve the
>desired result:

>SELECT firstname + ' ' + lastname AS fullname
>FROM tablename
>WHERE firstname + ' ' + lastname = 'john smith'
>ORDER BY fullname

>Hope this helps



>> Why I can use an alias name in the where statement of a query?
>> Something link this:

>> select firstname + ' ' + lastname as fullname where fullname='john smith'
>> or
>> select firstname + ' ' + lastname as fullname order by fullname

Reason is the execution order:
First your DBMS selects;
Second your DBMS concatenates;
Third your DBMS sorts.

At the first step, your DBMS would have to know the trick to correctly split
'john smith' in firstname, space, lastname in order to use these data in the
WHERE clause. DBMS's are not that smart (and cannot be considering the
possible occurrence of spaces in textcolumns firstname, lastname).

The first moment the alias fullname points to meaningful data is after
concatenation, just in time for the sort.

Dan effectively uses his knowledge of real-world names to perform the
reverse of the concatenation a DBMS cannot.

Think about other operators:
select price*quantity as amount
etcetera ...

Theo Peterbroers



Sat, 05 Apr 2003 03:00:00 GMT
 
 [ 3 post ] 

 Relevant Pages 

1. IN clausole - MSSQL7/2000

2. ORDER BY clause and alias's

3. Wrong behaviour in Order by clause using Alias

4. Get an alias back in order to use it in the same SELECT

5. Alias - no more Alias users in 7

6. composing alias in 7.0 or logon alias

7. ORDER BY without SELECT clause (order by function)

8. Multiple Tables DTS Order- How do I make them run in Specified order (not Alphbet)

9. Q: Prepackaged Sales Order Processing solution for Mail-Order

10. Multiple Tables DTS Order- How do I make them run in Specified order (not Alphbet)

11. Converting a database from dictionary case insensitive sort order to binary sort order


 
Powered by phpBB® Forum Software