Solved

T-SQL Problem - Invalid Column Name

Posted on 2004-08-10
6
1,378 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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: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
Edgartown IT Case Study

Learn about Edgartown's quest to ensure the safety and security of the entire town's employee and citizen data. Read the case study!

 
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

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

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

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

756 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