Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

T-SQL Problem - Invalid Column Name

Posted on 2004-08-10
6
Medium Priority
?
1,398 Views
Last Modified: 2007-12-19
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

0
Comment
Question by:born4code
6 Comments
 
LVL 34

Accepted Solution

by:
arbert earned 2000 total points
ID: 11764801
You're right--to be able to use and alias, you would have to recode the same statement, or use a dervied table....

Post exactly what you're trying to do/convert.

Brett
0
 
LVL 70

Expert Comment

by:Scott Pletcher
ID: 11764837
Except that you can use an alias in an ORDER BY (presumably because the alias same has already been "processed" by the sort phase).
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 11764897
>>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.
0
Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

 
LVL 2

Author Comment

by:born4code
ID: 11779936
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+1) AS FaceValue

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?
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 11780042
>>Any ideas?<<
As arbert has pointed out, you will have to repeat it or use a derived table.
0
 
LVL 34

Expert Comment

by:arbert
ID: 11780115
Ya, I was pretty sure of what you were saying in your original question.....
0

Featured Post

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Question has a verified solution.

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

In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

782 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