How to handle multiple Aliases

Question

Post reply

How to handle multiple Aliases

protocoder

Old Hand

Points: 374
More actions
April 13, 2022 at 11:04 pm
Go to Answer
#4015369
Hi Experts.
We have several views over views created and trying to consolidate them to base tables as part of cleanup. The views created alias to various Calculations used in different reports.
Is there a smart way to multiple alias same calculations and References
Example:
```
CASE WHEN A.CALC_Date_EOIReceived = '1900-01-01 00:00:00.000' THEN NULL ELSE A.CALC_Date_EOIReceived END [Date Req Created] 
```
This is also called [Date Initiated] in another view and all these are now in reports.
As of now, i am repeating the calculations and doing this way (So I need help, if this can be aliased in the beginning )
```
CASE WHEN A.CALC_Date_EOIReceived = '1900-01-01 00:00:00.000' THEN NULL ELSE A.CALC_Date_EOIReceived END [Date Req Created] 

CASE WHEN A.CALC_Date_EOIReceived = '1900-01-01 00:00:00.000' THEN NULL ELSE A.CALC_Date_EOIReceived END [Date Initiated]
```
Sometimes, I have to repeat more than 2 times. 🙁 just to get alias.
Help, Advice on how best to handle will be greatly appreciated.
Jeff Moden

SSC Guru

Points: 1004329
More actions
April 14, 2022 at 4:41 am
Answer
#4015455
One possibility for those is a bit shorter...
```
NULLIF(A.CALC_Date_EOIReceived,'1900-01-01 00:00:00.000') AS [Date Req Created]
NULLIF(A.CALC_Date_EOIReceived,'1900-01-01 00:00:00.000') AS [Date Initiated]
```
If we could see the rest of the code, we could probably "DRY" that out a whole lot more with either a CTE or a CROSS APPLY although the CROSS APPLY can sometimes make things a bit slower.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

protocoder Old Hand Points: 374 More actions · Answer 1

@jeff Moden

Thanks a lot, I have almost finished and submitted. Firstly thank you for the NULLIF.. that is my learning.

2. Once I am done with this, I will create a separate thread with this link and seek help on other smart ways..

Thanks a ton and

Happy Easter / Holidays

ScottPletcher SSC Guru Points: 101097 More actions · Answer 2

Yes, there is a better way. You can use CROSS APPLY(s) to assign alias name(s).

SELECT [Date Req Created], [Date Initiated], ...
FROM ...
CROSS APPLY (
    SELECT NULLIF(A.CALC_Date_EOIReceived, '19000101') AS [Date Req Created]
) AS ca1
CROSS APPLY (
    SELECT [Date Req Created] AS [Date Initiated]
) AS ca2

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".