Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

sql server - The Impact of Logical Processing Order on Aliases

Posted on 2012-09-11
2
Medium Priority
?
306 Views
Last Modified: 2012-09-12
Hi experts i am reading about:The Impact of Logical Processing Order on Aliases
but i do not understand:
Expressions aliased in SELECT clause may be repeated elsewhere in query

can explain me with an example, like hands on lab
0
Comment
Question by:enrique_aeo
2 Comments
 

Author Comment

by:enrique_aeo
ID: 38389328
please experts
what the mean
Note: the reason why an alias created in a SELECT clause may not be referenced elsewhere in the clause is due to the all-at-once processing implemented by SQL Server.
0
 
LVL 25

Accepted Solution

by:
lwadwell earned 2000 total points
ID: 38389903
Have a look at this example.
IF OBJECT_ID('tempdb..#eg_table')IS NOT NULL DROP TABLE #eg_table ; 
CREATE TABLE #eg_table(
	id 		INT,
	val   VARCHAR(30),
  amt1	DECIMAL(8,2),
  amt2	DECIMAL(8,2)
);
insert into #eg_table values (1,'test1',100,200),(2,'test2',12.34,65.43),(3,'test3',888,777);

-- example 1: create aliases on columns in SELECT clause
select id
     , val   as [the row description]  -- with an alias
     , amt1  as [first amount]         -- with an alias
     , amt2  as [second amount]        -- with an alias
     , amt1 + amt2 as [total amount]   -- with an alias
  from #eg_table;

-- example 2: use alias in ORDER BY
select id
     , val   as [the row description]  -- with an alias
     , amt1  as [first amount]         -- with an alias
     , amt2  as [second amount]        -- with an alias
     , amt1 + amt2 as [total amount]   -- with an alias
  from #eg_table
 order by [first amount];

-- example 3: use alias in WHERE
-- note: syntax error as WHERE is processed before SELECT,
--       hence alias not assigned yet.
select id
     , val   as [the row description]  -- with an alias
     , amt1  as [first amount]         -- with an alias
     , amt2  as [second amount]        -- with an alias
     , amt1 + amt2 as [total amount]   -- with an alias
  from #eg_table
 where [first amount] > 50;

-- example 4: use alias 'again' in SELECT
-- note: syntax error as SELECT processes all columns at the same
--       time, each column is unaware of the alias on another.
select id
     , val   as [the row description]  -- with an alias
     , amt1  as [first amount]         -- with an alias
     , amt2  as [second amount]        -- with an alias
     , [first amount] + [second amount] as [total amount]
  from #eg_table
 where [first amount] > 50;

Open in new window

Additional source: http://blog.sqlauthority.com/2009/04/06/sql-server-logical-query-processing-phases-order-of-statement-execution/
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Naughty Me. While I was changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notified my application fellows that I did it. They turn on the application, and everything is working. A …
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
In a question here at Experts Exchange (https://www.experts-exchange.com/questions/29062564/Adobe-acrobat-reader-DC.html), a member asked how to create a signature in Adobe Acrobat Reader DC (the free Reader product, not the paid, full Acrobat produ…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …

571 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question