• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 433
  • Last Modified:

refer to new field in query

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
cdhbs
Asked:
cdhbs
2 Solutions
 
jdlambert1Commented:
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
 
Scott PletcherSenior DBACommented:
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
 
waelothmanCommented:
why not
SELECT X+1 AS Y, X+2 AS Z
FROM myTable

0
 
cdhbsAuthor Commented:
Thanks, the subquery is obviously the way to go in my case - Y in this case is actually 700 lines of code.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now