Solved

T-SQL Problem - Invalid Column Name

Posted on 2004-08-10
6
1,379 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
PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

 
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

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL Percentage Formula 7 33
Moving away from Access 2003 adp files 4 48
HIghlights of SSIS? 3 45
Migrate SQL 2005 DB to SQL 2016 4 32
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…
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

751 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