?
Solved

T-SQL Problem - Invalid Column Name

Posted on 2004-08-10
6
Medium Priority
?
1,385 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 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
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 
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

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

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

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…
Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

718 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