Solved

refer to new field in query

Posted on 2004-10-13
4
401 Views
Last Modified: 2008-01-09
How do I refer to a newly created field in a query
for example

SELECT X+1 AS Y, Y+1 AS Z
FROM myTable

In the above I get an error when I try to refer to Y - this would work OK in MS Access
0
Comment
Question by:cdhbs
4 Comments
 
LVL 15

Accepted Solution

by:
jdlambert1 earned 65 total points
ID: 12303573
You can't refer to a column alias in the same SELECT statement. You can use a subquery or better, just repeat the formula (or it's logic combined with the additional logic):

SELECT X+1 AS Y, X+2 AS Z
FROM myTable
0
 
LVL 69

Assisted Solution

by:Scott Pletcher
Scott Pletcher earned 60 total points
ID: 12303593
If it's a complex expression, the subquery method might be easier:


SELECT y, y+2 AS z
FROM (
    SELECT x+1 AS y
    FROM myTable
) AS derived


Note that you must provide an alias for the derived query ("AS derived") and be sure not to use the original table name (such as "AS myTable") -- that can "confuse" SQL Server a lot, so just don't do it.
0
 
LVL 5

Expert Comment

by:waelothman
ID: 12304036
why not
SELECT X+1 AS Y, X+2 AS Z
FROM myTable

0
 

Author Comment

by:cdhbs
ID: 12304078
Thanks, the subquery is obviously the way to go in my case - Y in this case is actually 700 lines of code.
0

Featured Post

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL date incremented 11 31
sql server insert 12 30
SQL Error - Query 6 26
Need help with a Stored Proc on Sql Server 2012 4 11
Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

803 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