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

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

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

Author

Topic

shumailh
Starting Member

4 Posts

Posted - 2010-03-16 : 10:07:51

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-16 : 10:16:51

i think reason is SUM((commission_amount_recieved_usd)*((100-pl.incentive_per)/100) part of which comes from outer table. can i ask what you're trying to achieve by above query?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

shumailh
Starting Member

4 Posts

Posted - 2010-03-16 : 11:08:04

Ok, In the third field of my query i am trying to get the percentage of "commission_amount_recieved_usd" by multiplying it to the "pl.incentive_per" field.

please see the link below for the tables,

[url]http://www.sqlservercentral.com/Forums/Topic883698-391-1.aspx
[/url]

quote:
Originally posted by visakh16

i think reason is SUM((commission_amount_recieved_usd)*((100-pl.incentive_per)/100) part of which comes from outer table. can i ask what you're trying to achieve by above query?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-16 : 11:25:28

whats the purpose of this?

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)))

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

shumailh
Starting Member

4 Posts

Posted - 2010-03-17 : 02:20:31

As i cannot use aggregate values in where clause so i compare my 2 values i.e. max and min in having clause. to get the range between that aggregated data.

quote:
Originally posted by visakh16

whats the purpose of this?

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)))

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-17 : 03:47:31

nope i was asking why you consider outer table column for it?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

shumailh
Starting Member

4 Posts

Posted - 2010-03-17 : 09:30:57

because i think there is no other way to join "slab" table with "policy_dump" to get the "rate" field for the aggregate value of "sum(commission_amount_recieved_usd)"

you can try it by uploading that attached excel file in my previous link.

I am very week in sql, I would appreciate if you can help me out in this.

Regards,
Shumail Hussain

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources