CODEWITHSUNDEEP
sqlsql-serversql-server-2008-r2
CodeDada
CodeDada

Reputation: 435

Can alias column used in a view for calculation in some other column?

CREATE VIEW dbo.myview1 As
SELECT 
a.Id , 
a.Name , 
a.Age , 
CASE 
WHEN b.PmtSched ='Monthly'      THEN 12
WHEN b.PmtSched ='Quarterly'    THEN 4
WHEN b.PmtSched ='Semi-Annual'  THEN 2
WHEN b.PmtSched ='Annually'     THEN 1
ELSE 12
END AS ABC,
SUM(a.Amount) *50 as TotalAmount ,
(a.AmtSpent - TotalAmount) * ABC as TOTALSPENDS 

FROM dbo.User a join dbo.Details b on a.Id = b.Id

Here ABC and TotalAmount are Alias columns which needs to be used in computation in view and i am not able to use them.how to achieve this ?is there any way we could do this or we cant ?please help.

Upvotes: 1

Views: 80

Answers (3)

Ragul
Ragul

Reputation: 512

Simple answer "NO"

It's impossible to do this without a subquery.

or

You need to use CTE

Below query will help you to get what you need.

;WITH Amount
(
SELECT a.Id,a.NAME,a.Age,a.AmtSpent,
            CASE WHEN b.PmtSched ='Monthly'      THEN 12
                 WHEN b.PmtSched ='Quarterly'    THEN 4
                 WHEN b.PmtSched ='Semi-Annual'  THEN 2
                 WHEN b.PmtSched ='Annually'     THEN 1
            ELSE 12
            END AS ABC
           ,SUM(a.Amount) * 50 AS TotalAmount
    FROM 
        dbo.[User] a
    INNER JOIN
        dbo.Details b ON a.Id = b.Id
    GROUP BY id, NAME, age, abc, a.AmtSpent, TotalAmount
)

Now you can call those alias for calculation.

SELECT id,NAME,age,abc,(a.AmtSpent - TotalAmount) * ABC AS TOTALSPENDS FROM Amount

Upvotes: 1

Evaldas Buinauskas
Evaldas Buinauskas

Reputation: 14077

Yes, you can use it and you don't need neither subqueries, nor CTEs. It's a simple CROSS APPLY. It's quite elegant and doesn't hurt readability. If you need more information, read here.

Please see this example:

CREATE VIEW dbo.myview1
AS
SELECT A.Id
    , A.Name
    , A.Age
    , SUM(A.Amount) * 50 AS TotalAmount
    , (A.AmtSpent - TotalAmount) * T.ABC AS TotalSpends
FROM dbo.[User] AS A
CROSS APPLY (
    SELECT CASE B.PmtSched
            WHEN 'Monthly' THEN 12
            WHEN 'Quarterly' THEN 4
            WHEN 'Semi-Annual' THEN 2
            WHEN 'Annually' THEN 1
            ELSE 12
        END) AS T(ABC)
INNER JOIN dbo.Details AS B
    ON A.Id = B.Id;

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269953

The simple solution to your problem is to repeat the expression, use a subquery, or use a CTE.

However, the more intelligent method is to add a reference table for payment schedules. This would look like:

create table PaymentSchedules (
    PaymentScheduleId int identity(1, 1) primary key,
    ScheduleName varchar(255),
    FrequencyPerYear float  -- this could be less often than once per year
);

Then the view would look like:

CREATE VIEW dbo.myview1 As
    SELECT a.Id, a.Name, a.Age, ps.FrequencyPerYear, 
           SUM(a.Amount) * 50 as TotalAmount,
           (a.AmtSpent - SUM(a.Amount) * 50) * ps.FrequencyPerYear as TOTALSPENDS 
    FROM dbo.User a join
         dbo.Details b
         on a.Id = b.Id join
         dbo.PaymentSchedules ps
         on ps.PaymentScheduleId = a.PamentScheduleId;

Upvotes: 5

Related Questions