Reference an alias for an expression in a SELECT statement

Question

I have the following select statement that creates the expression "newdate" and then references in a different expression called "newname":

SELECT Iif([DateColumn1]='',Format(CONVERT(DATE,Dateadd(day,RIGHT([DateColumn2],3)-1,CONVERT(DATETIME,LEFT([DateColumn2],4)))),'MMddyy'),Format(CONVERT(DATE,Dateadd(day,RIGHT(datecolumn3],3)-1,CONVERT(DATETIME,LEFT([DateColumn3],4)))),'MMddyy')) AS **newdate**, Upper(LEFT(Rtrim([NameColumn4]),19)+''+[newdate]+''+RIGHT([NameColumn5],3)) AS newname FROM table1

For some reason when running this I get an error of invalid column and it refers to the "newdate" expression I created. The "newdate" expression works fine but it's when I add the second expression for "newname" that it stops working. Is there a way to refer to an expression as an alias within another expression that also has an alias?

Gordon Linoff · Accepted Answer · 2020-09-03 23:55:17Z

You can't. But SQL Server has a very nifty feature where you can define the column in the from clause using apply:

SELECT v.newdate, Upper(LEFT(Rtrim(t1.[NameColumn4]),19) + v.newdate + RIGHT(t1.[NameColumn5], 3)) AS newname FROM table1 t1 CROSS APPLY (VALUES ( <complicated expression here) ) v(newdate)

@Roger, Please mark it as answer. so,it is helpful for others in future.

Dale K · Accepted Answer · 2020-09-06 20:14:56Z

You can't reference an alias within the same scope as its created - you can create it in a sub-query if you wish to avoid repeating the logic.

SELECT UPPER(LEFT(RTRIM([NameColumn4]),19)+''+[newdate]+''+RIGHT([NameColumn5],3)) AS [NewName] FROM ( SELECT NameColumn4, NameColumn5 , IIF([DateColumn1]='',FORMAT(CONVERT(DATE,DATEADD(DAY,RIGHT([DateColumn2],3)-1,CONVERT(DATETIME,LEFT([DateColumn2],4)))),'MMddyy'),FORMAT(CONVERT(DATE,DATEADD(DAY,RIGHT(datecolumn3],3)-1,CONVERT(DATETIME,LEFT([DateColumn3],4)))),'MMddyy')) AS NewDate FROM table1 ) X

Venkataraman R · Accepted Answer · 2020-09-04 04:31:22Z

In SQL Server, there is a concept called ALL AT ONCE, where all operations on a logical query execution phase (SELECT here).So, you are facing issue here.

Detailed Information on All at Once

SELECT FirstName + LastName AS FullName, Salutation + FullName -- Will throw error here FROM TableName

You can handle that through multiple ways:

Sub queries Dale K has suggested in another answer
Common Table Expression Given below

;WITH CTE_NewDate AS ( SELECT Iif([DateColumn1]='',Format(CONVERT(DATE,Dateadd(day,RIGHT([DateColumn2],3)-1,CONVERT(DATETIME,LEFT([DateColumn2],4)))),'MMddyy'),Format(CONVERT(DATE,Dateadd(day,RIGHT(datecolumn3],3)-1,CONVERT(DATETIME,LEFT([DateColumn3],4)))),'MMddyy')) AS newdate, NameColumn4, NameColumn5 FROM table1 ) SELECT newDate, Upper(LEFT(Rtrim([NameColumn4]),19)+''+[newdate]+''+RIGHT([NameColumn5],3)) AS newname FROM CTE_NewDate

Collectives™ on Stack Overflow

Reference an alias for an expression in a SELECT statement

3 Answers 3

1 Comment

Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Linked

Related