born4code
asked on
T-SQL Problem - Invalid Column Name
I was in the process of transforming a database out of Access into SQL Server, and re-writing some queries, i.e. "views", when I noticed something. It appears that TSQL will not allow the re-use of an alias in the query?
Here is what I was doing successfully in Access... let's say in the middle of my query somewhere I do...
--------------------------
Select myFieldA * 100 + myFieldB + 42 AS [myAliasField].....
and then later in the Access query, I can directly access the alias again without recreating it...
...(select continued)... select [myAliasField] as [myAliasCopy]
--------------------------
THE PROBLEM:
Perhaps there is some special syntax in TSQL to allow for this? TSQL gives me the error, "Invalid Column Name" when I try to access/select the alias a second time. It's hard to believe that this can be done in Access, but cannot be done in SQL Server. Can anyone shed some light on this one?
Thanks
Here is what I was doing successfully in Access... let's say in the middle of my query somewhere I do...
--------------------------
Select myFieldA * 100 + myFieldB + 42 AS [myAliasField].....
and then later in the Access query, I can directly access the alias again without recreating it...
...(select continued)... select [myAliasField] as [myAliasCopy]
--------------------------
THE PROBLEM:
Perhaps there is some special syntax in TSQL to allow for this? TSQL gives me the error, "Invalid Column Name" when I try to access/select the alias a second time. It's hard to believe that this can be done in Access, but cannot be done in SQL Server. Can anyone shed some light on this one?
Thanks
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Scott Pletcher
Except that you can use an alias in an ORDER BY (presumably because the alias same has already been "processed" by the sort phase).
>>Except that you can use an alias in an ORDER BY<<
Right and in MS Access that is not allowed, go figure.
By the way, the only reason I bring this up in this Topic Area, is becuase the questioner brought up discrepancies between T-SQL and the SQL dialect used by MS Access.
Right and in MS Access that is not allowed, go figure.
By the way, the only reason I bring this up in this Topic Area, is becuase the questioner brought up discrepancies between T-SQL and the SQL dialect used by MS Access.
ASKER
Sorry it took so long to follow up...
Well, if you want a sample, I can give you a piece of one query (that is pages long)... just so you get the idea. It really is nothing different than I posted the first time.
AvgFP * sum(MS_Data.[SP_STD_Qty]) * [SP_STD_Std_Corr] AS SP_STD_StdFace,
([SP_STD_StdFace] * (1+[SP_STD_Per])^vMS_LE.LE
You can see here, that I repeat the calculation "SP_STD_StdFace". I do not have to recalculate it for the second line. Same concept.
Any ideas?
Well, if you want a sample, I can give you a piece of one query (that is pages long)... just so you get the idea. It really is nothing different than I posted the first time.
AvgFP * sum(MS_Data.[SP_STD_Qty]) * [SP_STD_Std_Corr] AS SP_STD_StdFace,
([SP_STD_StdFace] * (1+[SP_STD_Per])^vMS_LE.LE
You can see here, that I repeat the calculation "SP_STD_StdFace". I do not have to recalculate it for the second line. Same concept.
Any ideas?
>>Any ideas?<<
As arbert has pointed out, you will have to repeat it or use a derived table.
As arbert has pointed out, you will have to repeat it or use a derived table.
Ya, I was pretty sure of what you were saying in your original question.....