Query Issue
-
p
-
raghu (7/1/2009)
Update RequestsSet ER.[Part] = SUM(CAST(PT.[PREM] AS money)*Cred.CreditSigned / 100)
FROM Requests ER JOIN tblBuyinPtB Pt
ON ER.[SSN] = PT.[BOSSN] JOIN dbo.tblCredit Cred
ON Cred.
= Pt.
WHERE Pt.RIC is null OR Pt.RIC ='B'
GROUP BY Pt.BON
Please Help me writing this.......I am an error:getting incorrect syntax near group by
That would be because the update statement doesn't support a group by clause.
It looks like you would need to use a derived table here. Something like this:
update Requests
set ER.[Part] = t.Value
from Requests ER
INNER JOIN (select [BOSSN],
Value = SUM(CAST(PT.[PREM] AS money)*Cred.CreditSigned / 100)
from tblBuyinPtB Pt
JOIN dbo.tblCredit Cred ON Cred.
= Pt.
WHERE Pt.RIC is null OR Pt.RIC ='B'
GROUP BY Pt.[BOSSN]) t ON ER.[SSN] = t.[BOSSN]
Note that this code is untested... you did not supply table definitions or table data. If it doesn't work, please provide this information. See the link in my signature for how to do this.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2 -
Thank you very much .......This is working fine
-
raghu,
May I ask why you felt it necessary to delete your initial post? The problem you were having may be useful to someone else; without your problem description they would never know it the answer would help them also.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2 -
raghu (7/1/2009)
p--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
WayneS (7/1/2009)
raghu,May I ask why you felt it necessary to delete your initial post? The problem you were having may be useful to someone else; without your problem description they would never know it the answer would help them also.
At least you had quoted the original post and he didn't completely change the problem on us like "someone else" on another thread did after I posted a solution to his code.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply