T-SQL Visual Studio - Alias

Is it possible to reference an alias in the same query where it was declared?

Alias1 = Alpha
Alias2 = Beta

Can I write

Alias3 = Alpha + Beta
ShawnGrayAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Assuming both are character values...

SELECT Alpha + Beta as Alias3
FROM ...
0
sameer2010Commented:
No you cannot. You can write an outer select to do so.
0
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
<clarification>

You can always refer to the source columns as another calculated column, such as
SELECT Alpha as Alias1, Beta as Alias2, Alpha + Beta as Alias3
FROM...

What you can't do is refer to an alias inside an expression for another calculated column,
SELECT Alpha as Alias1, Beta as Alias2, Alias1 + Alias2 as Alias3

As Sameer states above you'd need a JOIN of some sort to pull that off.
If you'd like, give us a 'for example' of what you're trying to do, and we'll be able to come up with a more direct solution.
0
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

ShawnGrayAuthor Commented:
This gets pretty hairy.
I'm trying to determine the NextDistribution and PreviousDistribution from an account based on a fields:
[DistFreq], [DistDay], and [DistAmount]
[DistFreq] values = Monthly, Qrt-1-4-7-10, Qrt-2-5-8-11, Qrt-3-6-9-12, Ann-Jan, Ann-Feb...Ann-Dec
[DistDay] = 1, 2 ...28, 0 (end of month), -1 (day before end of month)

As an example,
Name   DistFreq        DistDay     DistAmount
John       Qrt-1-4-7-10    3                  $1,000
Sam       Monthly            0                  $   500
Ben        Ann-Nov           10                $1,000  

Trying to find:
NextDistribution = 10/3/13
PreviousDistribution = 7/3/13

These dates are then compared with a transaction table that look at
PreviousDistribution and confirm it was paid and
NextDistribution is an alert to make certain cash is available to distribute (another table)

All records are only concerned with those + or - 7 days from now()

I had this working in MSAccess with some really, really long conditions which referenced aliases in my query.  Moving this to SQL is proving to be a bit more complicated to recreate.
Partly because of the alias limitation and partly because I'm not as familiar with writing sql as I am developing in Access.
0
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
>I'm trying to determine the NextDistribution and PreviousDistribution
SQL 2012 introduces the LAG and LEAD functions that allow you to access the previous and next rows in a time series.  Very damn handy.

The only other way pre-2012 I'm familiar with to pull this off would be to write a Common Table Expression (CTE) in SQL, with two copies of a table where the dates are ranked, and JOINed by the previous rank.  For a working example see my article on T-SQL:  How to identify bad dates in a time series, specifically in the middle that begins with 'The guts of this functionality is a single CTE expression', with the below code
  FROM   date_sort_order base
        -- Compare dates with previous row
        LEFT JOIN date_sort_order compare ON base.policy_number = compare.policy_number AND base.date_sort_order = (compare.date_sort_order - 1)

Open in new window

>I'm not as familiar with writing sql as I am developing in Access.
I converted from Access to SQL Server about seven years ago.  Once you realize that you can take an access query, string them together, create and manipulate temp tables, create a loop, and write code comments in a single script, SQL becomes highly addictive.

Also in case you get buried in calendar math, here's an article on Building your own calendar table to perform complex date expressions that will help.

Good luck.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
ShawnGrayAuthor Commented:
Thanks Jim; this is helpful.
0
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Thanks for the grade.  Good luck with your project.  -Jimbo
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.