Server: Msg 8124, Level 16, State 1, Procedure

Question

Post reply

Server: Msg 8124, Level 16, State 1, Procedure

shumail.hussain

Old Hand

Points: 388
More actions
March 16, 2010 at 6:20 am

#386512

Below is the one sp which i am using to get my MIS but what i found this sp is inefficient as i am using subquery under select statement of base query
plus i fail to resolve the error which i am getting on field [Over_Achv] as below
I get this error:
Server: Msg 8124, Level 16, State 1, Procedure spComission_Quarterly, Line 16
Multiple columns are specified in an aggregated expression containing an outer reference. If an expression being aggregated contains an outer reference, then that outer reference must be the only column referenced in the expression.
SELECT
ro_staff_id as RO_Code,
SUM(ClawBack_Amount_USD) as ClawBack,
SUM(commission_amount_recieved_usd)*
(SELECT MAX(RATE) AS RATE FROM
[Bancassurance].[dbo].SLAB SS1
WHERE
SS1.TYPE = 'RO'
GROUP BY
SS1.MIN_AMOUNT, SS1.MAX_AMOUNT
HAVING
SS1.MIN_AMOUNT <= (SUM((commission_amount_recieved_usd)*((100-pl.incentive_per)/100))) and
SS1.MAX_AMOUNT >= (SUM((commission_amount_recieved_usd)*((100-pl.incentive_per)/100)))
)
FROM policy_dump as A
LEFT OUTER JOIN [Bancassurance].[dbo].RM R ON R.RMCODE = A.RO_STAFF_ID
LEFT OUTER JOIN [Bancassurance].[dbo].[Plan] Pl ON Pl.Plan_ID = A.Plan_ID
GROUP BY ro_staff_id
would appreciate if anyone could help me out in this

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Paul White SSC Guru Points: 150468 More actions · Answer 1

Way too much code. If you can present your problem using a simple example, you will get a better answer, faster.

Paul White
All articles available on SQL.kiwi
@SQL_Kiwi

shumail.hussain Old Hand Points: 388 More actions · Answer 2

[font="Verdana"]below is the short example and i am facing error on having clause,

Server: Msg 8124, Level 16, State 1, Line 3

Multiple columns are specified in an aggregated expression containing an outer reference. If an expression being aggregated contains an outer reference, then that outer reference must be the only column referenced in the expression.

SELECT

ro_staff_id as RO_Code,

SUM(ClawBack_Amount_USD) as ClawBack,

SUM(commission_amount_recieved_usd)*

(SELECT MAX(RATE) AS RATE FROM

[Bancassurance].[dbo].SLAB SS1

WHERE

SS1.TYPE = 'RO'

GROUP BY

SS1.MIN_AMOUNT, SS1.MAX_AMOUNT

HAVING

SS1.MIN_AMOUNT <= (SUM((commission_amount_recieved_usd)*((100-pl.incentive_per)/100))) and

SS1.MAX_AMOUNT >= (SUM((commission_amount_recieved_usd)*((100-pl.incentive_per)/100)))

)

FROM policy_dump as A

LEFT OUTER JOIN [Bancassurance].[dbo].RM R ON R.RMCODE = A.RO_STAFF_ID

LEFT OUTER JOIN [Bancassurance].[dbo].[Plan] Pl ON Pl.Plan_ID = A.Plan_ID

GROUP BY ro_staff_id[/font]

shumail.hussain Old Hand Points: 388 More actions · Answer 3

also please see the attached schema's example,

regards,

Shumail