0

This question is in relation to user-defined scalar function to generate computed column

ALTER TABLE dbo.Installment ADD SurchargeCalculated AS (select Amount * Days * InstSurchargePercentage / 365 / 100 from InstallMentPlan where InstallMentPlan.PlanKey = Installment.PlanKey ) GO

Error is:

Msg 1046, Level 15, State 1, Line 3
Subqueries are not allowed in this context. Only scalar expressions are allowed.

Improve this question
1
  • 1
    Scalar functions, however, tend to have a performance hit (note, I can't speak for them on computed columns in SQL Server 2019 with it's inlining functionality) and can cause race conditions. This would seem like a better candidate for a VIEW or similar. Commented Feb 17, 2020 at 16:26

1 Answer 1

Reset to default
2

You can't do this with a computed column: it may reference solely columns from the same table and same row.

As a workaround, you could define a view:

create view dbo.InstallmentView as select i.*, ( select p.Amount * p.Days * p.InstSurchargePercentage / 365 / 100 from dbo.InstallMentPlan p where p.PlanKey = i.PlanKey ) SurchargeCalculated from dbo.Installment i

This can also be expressed with a join:

create view dbo.InstallmentView as select i.*, p.Amount * p.Days * p.InstSurchargePercentage / 365 / 100 SurchargeCalculated from dbo.Installment i left join dbo.InstallMentPlan p on p.PlanKey = i.PlanKey

Note that you should enumerate the columns that you want to select form dbo.Installment instead of using * (I did so only because I have no knowledge of your database structure).

Improve this answer
Sign up to request clarification or add additional context in comments.

5 Comments

thank you very much for your reply...it throws this error now...Msg 207, Level 16, State 1, Procedure InstallmentView, Line 6 Invalid column name 'PlanKey'. Msg 207, Level 16, State 1, Procedure InstallmentView, Line 6 Invalid column name 'PlanKey'.
@khawarabbasi: well, do you have a column called PlanKey in tables dbo.Installment and dbo.InstallMentPlan? I took that from your original query.
I have these columns in Installment table ,[PlotId] ,[MemberId] ,[PlotFileId] ,[InstallmentNo] ,[InstallmentOrder] ,[Amount] ,[DueDate] ,[AmountPaid] ,[PaidOn] ,[Surcharge] ,[Comments] ,[ModifiedOn] ,[SyncState] ,[SurchargePaid] ,[SurchargePaidOn] ,[PartialInstallmentId] ,[InstPlanDetailId] ,[days] ,[Is_Lumpsum]
and InstallmentPlan col=> [SizeId] ,[PhaseId] ,[BlockId] ,[PlanName] ,[TotalCost] ,[DownPayment] ,[NoOfInstallment] ,[InstallmentAmount] ,[StartDate] ,[ModifiedOn] ,[DownPaymentDueDate] ,[InstallmentDurationMonth] ,[DPSurchargeAmount] ,[DPSurchargePercentage] ,[InstSurchargeAmount] ,[InstSurchargePercentage] ,[InstSurchargeDueMonth] ,[DueDateAdjustment] ,[LumsumDiscount] ,[LumsumDiscountDueDate]
@khawarabbasi: in your original query, there is a column called PlanKey in both tables, so I used it as a join condition. If that's not correct, then you need to change that to the relevant columns. I cannot tell which columns can be used (this is your database!).

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.