|
|
Hi i am having trouble tryin to call a function 4 times in the same statement but with different parameters this is the function
CREATE FUNCTION [dbo].[pr_bll_GetGoalkeeperMarks]
(@PlayerID int,
@Ability nvarchar(120)) returns int
--WITH ENCRYPTION
BEGIN
DECLARE @result int
select @result = case @Ability
WHEN 'Athletic' THEN ScoutingGoalkeeperAthleticAbility.Agility+ScoutingGoalkeeperAthleticAbility.Reactions+ScoutingGoalkeeperAthleticAbility.Bodystrength --Athletic
WHEN 'Character' THEN ScoutingGoalkeeperCharacter.Confidence+ScoutingGoalkeeperCharacter.Bravery+ScoutingGoalkeeperCharacter.Leadership+ScoutingGoalkeeperCharacter.Energetic+ScoutingGoalkeeperCharacter.RiskTaker+ScoutingGoalkeeperCharacter.Talkative+ScoutingGoalkeeperCharacter.Competitive+ScoutingGoalkeeperCharacter.Presence --Character
WHEN 'Tactical' THEN ScoutingGoalkeeperTacticalAbility.Vision+ScoutingGoalkeeperTacticalAbility.Awareness+ScoutingGoalkeeperTacticalAbility.Anticipation+ScoutingGoalkeeperTacticalAbility.StartingPosition --Tactical
WHEN 'Technical' THEN ScoutingGoalkeeperTechnicalAbility.Handling+ScoutingGoalkeeperTechnicalAbility.Crosses+ScoutingGoalkeeperTechnicalAbility.ShotStopping+ScoutingGoalkeeperTechnicalAbility.KickingDeadBall+ScoutingGoalkeeperTechnicalAbility.KickingHands+ScoutingGoalkeeperTechnicalAbility.Distribution+ScoutingGoalkeeperTechnicalAbility.BackPasses+ScoutingGoalkeeperTechnicalAbility.Positioning+ScoutingGoalkeeperTechnicalAbility.CommandOfArea+ScoutingGoalkeeperTechnicalAbility.Sweeping+ScoutingGoalkeeperTechnicalAbility.ComingOffLine --Technical
end
from
ScoutingReport sr
Join Person p on p.Id = sr.ScoutID
left join ScoutingDefenderTechnicalAbility on ScoutingDefenderTechnicalAbility.ScoutingReportID = sr.Id
left join ScoutingForwardTechnicalAbility on ScoutingForwardTechnicalAbility.ScoutingReportID = sr.Id
left join ScoutingGoalkeeperAthleticAbility on ScoutingGoalkeeperAthleticAbility.ScoutingReportID = sr.Id
left join ScoutingGoalkeeperCharacter on ScoutingGoalkeeperCharacter.ScoutingReportID = sr.Id
left join ScoutingGoalkeeperTacticalAbility on ScoutingGoalkeeperTacticalAbility.ScoutingReportID = sr.Id
left join ScoutingGoalkeeperTechnicalAbility on ScoutingGoalkeeperTechnicalAbility.ScoutingReportID = sr.Id
left join ScoutingMidfielderTechnicalAbility on ScoutingMidfielderTechnicalAbility.ScoutingReportID = sr.Id
left join ScoutingOutfieldAthleticAbility on ScoutingOutfieldAthleticAbility.ScoutingReportID = sr.Id
left join ScoutingOutfieldCharacter on ScoutingOutfieldCharacter.ScoutingReportID = sr.Id
left join ScoutingOutfieldTacticalAbility on ScoutingOutfieldTacticalAbility.ScoutingReportID = sr.Id
where
sr.ScoutedPersonId = @PlayerId and
sr.type=0
return @result
END
playerid and ability parameters are passed in and a count of that ability for that player is passed back it works fine when the statement doesnt try to get the average of each ability but when this is included the results are the same for each of the four calls im just wondering if anyone has any idea why this is happenin thanks in advance
select avg(dbo.pr_bll_GetGoalkeeperMarks(scoutedPersonID, 'Athletic')) as 'Athletic',
avg(dbo.pr_bll_GetGoalkeeperMarks(scoutedPersonID, 'Character')) as 'Character',
avg(dbo.pr_bll_GetGoalkeeperMarks(scoutedPersonID, 'Tactical')) as 'Tactical',
avg(dbo.pr_bll_GetGoalkeeperMarks(scoutedPersonID, 'Technical')) as 'Technical',
avg(dbo.pr_bll_GetPlayerTotalMarks(scoutedPersonID))
from scoutingreport sr
Where sr.type = 0
And ScoutId = 11619
And sr.Position in (Select ID From ScoutingPosition Where PositionGroup = 0)
-- modified at 6:45 Wednesday 28th June, 2006
|
|
|
|
|
|
yes but it is commented out so i presume its havin no effect
|
|
|
|
|
|
ya thanks for the advice but that doesnt really help me with my problem does it
|
|
|
|
|
tadhg88 wrote: ya thanks for the advice but that doesnt really help me with my problem does it
If you're looking for help, politeness goes a long way - rudeness gets nowhere.
Colin Angus Mackay knows as much about SQL as anyone I've seen on The Code Project. I'd be nice to him if I were you.
----------
Some problems are so complex that you have to be highly intelligent and well informed just to be undecided about them.
- Laurence J. Peters
|
|
|
|
|
|
my apologies i read my post again today and it was a little rude so thanks for takin the time to even try to help IT IS appreciated
|
|
|
|
|
Have you tried to Group By scoutedPersonID?
Also, you have 'Tactical' as the second parameter for both the Athletic and Tactical columns. I assume that is not your intention.
----------
Some problems are so complex that you have to be highly intelligent and well informed just to be undecided about them.
- Laurence J. Peters
|
|
|
|
|
if i group by scoutedpersonid it will give me averages for each player but i want all players that were scouted by a specific scout and get the average of these
|
|
|
|
|
select sr.ScoutedPersonId, sr.ScoutID, ScoutingGoalkeeperAthleticAbility.Agility +ScoutingGoalkeeperAthleticAbility.Reactions <br />
+ScoutingGoalkeeperAthleticAbility.Bodystrength as Athletic,<br />
ScoutingGoalkeeperCharacter.Confidence +ScoutingGoalkeeperCharacter.Bravery +ScoutingGoalkeeperCharacter.Leadership +ScoutingGoalkeeperCharacter.Energetic +ScoutingGoalkeeperCharacter.RiskTaker +ScoutingGoalkeeperCharacter.Talkative +ScoutingGoalkeeperCharacter.Competitive +ScoutingGoalkeeperCharacter.Presence as Character, ScoutingGoalkeeperTacticalAbility.Vision +ScoutingGoalkeeperTacticalAbility.Awareness +ScoutingGoalkeeperTacticalAbility.Anticipation +ScoutingGoalkeeperTacticalAbility.StartingPosition as Tactical,<br />
ScoutingGoalkeeperTechnicalAbility.Handling +ScoutingGoalkeeperTechnicalAbility.Crosses +ScoutingGoalkeeperTechnicalAbility.ShotStopping +ScoutingGoalkeeperTechnicalAbility.KickingDeadBall +ScoutingGoalkeeperTechnicalAbility.KickingHands +ScoutingGoalkeeperTechnicalAbility.Distribution +ScoutingGoalkeeperTechnicalAbility.BackPasses +ScoutingGoalkeeperTechnicalAbility.Positioning +ScoutingGoalkeeperTechnicalAbility.CommandOfArea +ScoutingGoalkeeperTechnicalAbility.Sweeping +ScoutingGoalkeeperTechnicalAbility.ComingOffLine as Technical<br />
from<br />
ScoutingReport sr<br />
Join Person p on p.Id = sr.ScoutID<br />
left join ScoutingDefenderTechnicalAbility on ScoutingDefenderTechnicalAbility.ScoutingReportID = sr.Id<br />
left join ScoutingForwardTechnicalAbility on ScoutingForwardTechnicalAbility.ScoutingReportID = sr.Id<br />
left join ScoutingGoalkeeperAthleticAbility on ScoutingGoalkeeperAthleticAbility.ScoutingReportID = sr.Id<br />
left join ScoutingGoalkeeperCharacter on ScoutingGoalkeeperCharacter.ScoutingReportID = sr.Id<br />
left join ScoutingGoalkeeperTacticalAbility on ScoutingGoalkeeperTacticalAbility.ScoutingReportID = sr.Id<br />
left join ScoutingGoalkeeperTechnicalAbility on ScoutingGoalkeeperTechnicalAbility.ScoutingReportID = sr.Id<br />
left join ScoutingMidfielderTechnicalAbility on ScoutingMidfielderTechnicalAbility.ScoutingReportID = sr.Id<br />
left join ScoutingOutfieldAthleticAbility on ScoutingOutfieldAthleticAbility.ScoutingReportID = sr.Id<br />
left join ScoutingOutfieldCharacter on ScoutingOutfieldCharacter.ScoutingReportID = sr.Id<br />
left join ScoutingOutfieldTacticalAbility on ScoutingOutfieldTacticalAbility.ScoutingReportID = sr.Id<br />
where sr.type=0 <br />
and sr.Position in (Select ID From ScoutingPosition Where PositionGroup = 0)
To get averages for everyone scouted by a particular scout:
select avg(athletic) as athletic,avg(character) as character,avg(tactical) as tactical<br />
from vewScoutTotals<br />
where ScoutID = 11619
----------
Some problems are so complex that you have to be highly intelligent and well informed just to be undecided about them.
- Laurence J. Peters
|
|
|
|
|
yes that wud work but to be honest i just wanted to get a bit of experience with functions so i said id try and use them, ran into trouble and cud not understand why it wudnt work(still dont infact) but i guess i never will now
|
|
|
|
|
tadhg88 wrote: i just wanted to get a bit of experience with functions so i said id try and use them, ran into trouble and cud not understand why it wudnt work
Oh. Well, I too am mystified over the AVG() oddity. Sorry I have no answer for it. Maybe Colin can explain it to us when he gets a little more free time. 
--EricDV Sig---------
Some problems are so complex that you have to be highly intelligent and well informed just to be undecided about them.
- Laurence J. Peters
|
|
|
|
|
yes i think mystified is the best way to describe it but thanks anyway
|
|
|
|
|
Hi Friends,
I need to run a stored procedure to show the results in my crystal report.
I have just sql server and crystal report.
My problem is, I dont want my users to run that procedure manually can any one help me how can i runt that stored prodedure b4 the user runs crystal reports to get result from my Stroed procedure.
Thanks
Naveed Kamboh
-- modified at 7:09 Monday 26th June, 2006
|
|
|
|
|
Naveed Kamboh wrote: I dont want my users to run that procedure manually
Stored procedures are run from within the program code. Users don't just run them. Don't completely understand your question 
|
|
|
|
|
I have a stored procedure in SQLServer which inserts new employee records in a table.
Now i want to be able to insert a small image in the table as well, i can add an image if i use a simple ADO recordset but how do i do it in a stored procedure
Any help in any direction will be much appreciated
i could discard StoredProc and use Recordsets to add records but i would really not
You know you're obsessed with computer graphics when you're outside and you look up at the trees and think, "Wow! That's spectacular resolution!"
Only kings, presidents, editors, and people with tapeworms have the right to use the editorial "we."
|
|
|
|
|
Create a parameter of type image. Use the Parameters collection to pass parameters to your stored procedure (you should be using this anyway). The corresponding Parameter object should be of type adLongVarBinary. Set the parameter's Size to the amount of data you intend to pass, then use AppendChunk to set the data.
Stability. What an interesting concept. -- Chris Maunder
|
|
|
|
|
hi guys,
i have the following code,
Private Sub btnopendb_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnopendb.Click<br />
<br />
Try<br />
<br />
With OpenFileDialog1<br />
<br />
' Check to ensure that the selected file exists. Dialog box displays a warning otherwise.<br />
<br />
.CheckFileExists = True<br />
<br />
' Check to ensure that the selected path exists. Dialog box displays a warning otherwise.<br />
<br />
.CheckPathExists = True<br />
<br />
' Get or set default extension. Doesn't include the leading ".".<br />
<br />
.DefaultExt = "MDB"<br />
<br />
' Return the file referenced by a link? If False, simply returns the selected link file.<br />
' If True, returns the file linked to the LNK file.<br />
<br />
.DereferenceLinks = True<br />
<br />
.InitialDirectory = "C:\"<br />
<br />
.Filter = "mdb|*.mdb"<br />
<br />
.Multiselect = False<br />
<br />
.RestoreDirectory = True<br />
<br />
'.FilterIndex = 1<br />
<br />
End With<br />
<br />
If OpenFileDialog1.ShowDialog() = Windows.Forms.DialogResult.OK Then<br />
<br />
txtopendb.Text = OpenFileDialog1.FileName<br />
<br />
End If<br />
<br />
Catch exp As Exception<br />
MessageBox.Show(exp.Message, Me.Text)<br />
End Try<br />
<br />
<br />
End Sub<br />
i have a textbox where i give the path of database. when i give the path of database in textbbox, then i need to open the database in dtagrid. how do i do that?
thanks
|
|
|
|
|
Hi,
I am trying to run a CREATE TABLE statment using CDatabase.ExecuteSQL and ODBC connectivitity. This is in Win 98 and I am using Visual C++ 6.0. The SQL is read from a file placed into a buffer and then CDatabase (DBSQL.ExecuteSQL) is called. I have verified that there are no unintended characters in the SQL that is passed to Execute SQL and that the SQL stmt is terminated by a NULL character.
My SQL:
CREATE TABLE AddrBook
(FILE_NAME C (50) NOT NULL
)
The snippet of code I am using to do this is:
try {
nRC = DBSQL.Open(chdatabaseName,FALSE,FALSE,_T("ODBC;"));
}
catch (CDBException* e) {
cout<<"ERROR: "<<e->m_strError<<endl;
cout<<"Return Code is: "<<e->m_nRetCode<<endl;
return(nRC);
}
while (FileSQL.BuildSQLStmt()) {
try {
DBSQL.ExecuteSQL(FileSQL.SQLStmt.textP);
}
catch (CDBException* e) {
cout<<"ERROR: "<<e->m_strError<<endl;
cout<<"Return Code is: "<<e->m_nRetCode<<endl;
return(nRC);
}
The DBSQL.Connect appears to be working ok, that is I get a -1 RC and the catch exception code is not executed but I get a -1 RC and the message "Syntax error in Field Definition when the cach exception code is executed after the call to ExecuteSQL.
I have tried searching through different references for anything that would show me what I am doing wrong but have not found anything. Anyone have any ideas what I am doing wrong? I think there is something wrong with my Syntax but I don't know what is and I think may have looked at the wrong examples in the MSDN library.
Thanks!
|
|
|
|
|
C (50)
Shouldn't that be Char(50) ?
|
|
|
|
|
You are correct, I think the syntax chart I was using was for Visual Fox Pro. Thanks! 
|
|
|
|
|
hi
I opened my sql database and I found it to be in suspect mode so I detached it but now I can't Attach it
does anybody know how!! please help
|
|
|
|
|
You can use sp_attach_db or sp_attach_single_file_db.
Or:
CREATE DATABASE [MyDB] ON (FILENAME = 'C:\MyPath\MyDB_Data.MDF') FOR ATTACH
----------
Some problems are so complex that you have to be highly intelligent and well informed just to be undecided about them.
- Laurence J. Peters
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
