Solved

T-SQL Problem - Invalid Column Name

Posted on 2004-08-10
6
1,362 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 500 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 69

Expert Comment

by:ScottPletcher
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
Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
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

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Join & Write a Comment

Performance is the key factor for any successful data integration project, knowing the type of transformation that you’re using is the first step on optimizing the SSIS flow performance, by utilizing the correct transformation or the design alternat…
Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

757 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now