CODEWITHSUNDEEP
sql-serversql-server-2008t-sql
穆罕默德 - Moh
穆罕默德 - Moh

Reputation: 1373

could I use Alias in View in SQL Server and process?

could I use Alias in View in SQL Server and process in it?

for example, I have :

select dbo.DateToShamsi(dbo.Health_Komak2.CDate) as HK2Hijri .....

can I Use :

select dbo.DateToShamsi(dbo.Health_Komak2.CDate) as HK2Hijri , SUBSTRING(----->HK2Hijri, 1, 4)

if No how can I Do That?

Upvotes: 1

Views: 1460

Answers (2)

Lukasz Szozda
Lukasz Szozda

Reputation: 175756

could I use Alias in View in SQL Server and process in it?

No, explanation All-at-Once Operations in T-SQL

Invalid column name 'Corrected FirstName'.

This error message means that we cannot use an alias in next column expression in the SELECT clause. In the query we create a corrected first name and we want to use it in next column to produce the full name, but the All-at-Once operations concept tells us you cannot do this because all expressions in the same logical query process phase (here is SELECT) are evaluated logically at the same time.

You could use OUTER APPLY:

SELECT s.HK2Hijri, LEFT(s.HK2Hijri, 4)
FROM dbo.Health_Komak2 k
OUTER APPLY (SELECT dbo.DateToShamsi(k.CDate) AS HK2Hijri) s

Upvotes: 4

Gordon Linoff
Gordon Linoff

Reputation: 1269953

No, but you can do this using a subquery, CTE, or lateral join. Let me show the latter, which is the most fun of the options:

select v.HK2Hijri, substr(v.HK2Hijri, . . .
from dbo.Health_Komak2 hk outer apply
     (values (dbo.DateToShamsi(hk.CDate))
     ) v(HK2Hijri);

Apply is handy because you can define multiple aliases. You can also use apply multiple times to perform operations on previously defined aliases.

Upvotes: 4

Related Questions