Error in sp

1

when execuitng this stored procedure

select t.OF_Feescode , t.OF_FeeHeadname,t.OF_FeeAmount, concession = (SELECT OF_FeesPercentage
FROM OF_Concession where OS_IdNo='4734' and OF_AcademicYear=(select acdyear from OP_Academicyear)
and OF_FeesHeadCode in ( SELECT OF_FeeHeadcode FROM OF_FeeHeads WHERE OF_GroupCode=1) and
OF_Feescode = t.OF_Feescode
), totalfee = sum(t.OF_FeeAmount + concession)

from tempTable t

I got error as Invalid column name 'concession'.

Could anyone pls help me to solve this?

I want to get sum of OF_FeeAmount and concession

2

You cannot use an alias (concession in your example) that you have created in a select statement in another part of the select statement. So you will have to repeat the whole calculation, or do the calculation in a subquery or join. For example this should work:

SELECT  t.OF_Feescode ,
        t.OF_FeeHeadname ,
        t.OF_FeeAmount ,
        concession = OF_FeesPercentage,
        totalfee = SUM(t.OF_FeeAmount + OF_FeesPercentage)
FROM    tempTable t
CROSS APPLY
(
	SELECT   OF_FeesPercentage
	FROM     OF_Concession
	WHERE    OS_IdNo = '4734'
	AND OF_AcademicYear = ( SELECT
								  acdyear
							FROM  OP_Academicyear
						  )
	AND OF_FeesHeadCode IN ( SELECT
								  OF_FeeHeadcode
							 FROM OF_FeeHeads
							 WHERE
								  OF_GroupCode = 1 )
	AND OF_Feescode = t.OF_Feescode
) c
1 Like