Query question - SQL Server Forums

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

SQL Server Development (2000)

Query question

Author

Topic

JoeGonz
Starting Member

10 Posts

Posted - 2003-09-29 : 07:44:02

I had to reregister as I not only forgot my password but username. I've been here before and hope you guys are fine.

I have a fantasy type program that I thought I had working. Not so. It is supposed to first check an eligibility table to determine if the player is qualified. Stat is set to '1' if that is the case. If they are qualified then the procedure is then set to tally the scores and write to the table. My problem is not with the tallying as I have been using that code for months correctly. For some reason it is not recognizing the players and status in the ConStatus table. I have the code below. In fact, the only entries it puts in the table are the last player's. Whether he is qualified or not. Any help, thank you.

CREATE PROCEDURE FFDo AS
Declare @status as varchar(50), @player as varchar(50)
Select @status = Stat from ConStatus
Select @player = Player from ConStatus
If @status = '1'

INSERT INTO FFTable(Player,Sport,Game,Type,GameDate,Pick,Units,UE,Result)
SELECT Player,Sport,Game,Type,GTime,Selection,Units,
CASE WHEN AbbrvSel = 'H' AND Hscore + Tot > Vscore
THEN Units
WHEN AbbrvSel = 'H' AND Hscore + Tot < Vscore
THEN -1.1 * Units
WHEN AbbrvSel = 'V' AND Vscore + Tot < Hscore
THEN -1.1 * Units
WHEN AbbrvSel = 'V' AND Vscore + Tot > Hscore
THEN Units
ELSE 0 END AS UE,
CASE WHEN AbbrvSel = 'H' AND Hscore + Tot > Vscore THEN 'Win'
WHEN AbbrvSel = 'H' AND Hscore + Tot < Vscore THEN 'Loss'
WHEN AbbrvSel = 'V' AND Vscore + Tot < Hscore THEN 'Loss'
WHEN AbbrvSel = 'V' AND Vscore + Tot > Hscore THEN 'Win'
ELSE '0' END AS Result
FROM DTBackup WHERE Sport = 'NFL' AND Type = 'Side' AND @player = Player

INSERT INTO FFTable (Player,Sport,Game,Type,GameDate,Pick,Units,UE,Result)
SELECT Player,Sport,Game,Type,GTime,Selection,Units,
CASE WHEN AbbrvSel = 'O' AND Hscore + Vscore > Tot
THEN Units
WHEN AbbrvSel = 'O' AND Hscore + Vscore < Tot
THEN -1.1 * Units
WHEN AbbrvSel = 'U' AND Hscore + Vscore > Tot
THEN -1.1 * Units
WHEN AbbrvSel = 'U' AND Hscore + Vscore < Tot
THEN Units
ELSE 0 END AS UE,
CASE WHEN AbbrvSel = 'O' AND Hscore + Vscore > Tot THEN 'Win'
WHEN AbbrvSel = 'O' AND Hscore + Vscore < Tot THEN 'Loss'
WHEN AbbrvSel = 'U' AND Hscore + Vscore > Tot THEN 'Loss'
WHEN AbbrvSel = 'U' AND Hscore + Vscore < Tot THEN 'Win'
ELSE 'Push' END AS Result
FROM DTBackup WHERE Sport = 'NFL' AND Type = 'O/U' AND @player = Player
GO

JoeGonz
Starting Member

10 Posts

Posted - 2003-09-29 : 08:00:42

I changed the first part of the code to :
CREATE PROCEDURE FFDo AS
Declare @player as varchar(50)

Select @player = Player from ConStatus where Stat = '1'

Now I get the last name in the ConStatus table that is qualified. But none of the others. I know I am not too swift with this stuff, but I seldom use db in creating my websites.

X002548
Not Just a Number

15586 Posts

Posted - 2003-09-29 : 10:49:08

quote:
Originally posted by JoeGonz

I changed the first part of the code to :
CREATE PROCEDURE FFDo AS
Declare @player as varchar(50)

Select @player = Player from ConStatus where Stat = '1'

Now I get the last name in the ConStatus table that is qualified. But none of the others. I know I am not too swift with this stuff, but I seldom use db in creating my websites.

A variable can only hold 1 value at a time....you're trying to put n rows in...and it'll only take the last one...looks like you just need to combine the tables in to a join...

Brett

8-)

SELECT @@POST FROM Brain ORDER BY NewId()

That's correct! It's an AlphaNumeric!

JoeGonz
Starting Member

10 Posts

Posted - 2003-09-29 : 10:54:53

CREATE PROCEDURE FFD AS

INSERT INTO FFTable(Player,Sport,Game,Type,GameDate,Pick,Units,UE,Result)
SELECT Player,Sport,Game,Type,GTime,Selection,Units,
CASE WHEN AbbrvSel = 'H' AND Hscore + Tot > Vscore
THEN Units
WHEN AbbrvSel = 'H' AND Hscore + Tot < Vscore
THEN -1.1 * Units
WHEN AbbrvSel = 'V' AND Vscore + Tot < Hscore
THEN -1.1 * Units
WHEN AbbrvSel = 'V' AND Vscore + Tot > Hscore
THEN Units
ELSE 0 END AS UE,
CASE WHEN AbbrvSel = 'H' AND Hscore + Tot > Vscore THEN 'Win'
WHEN AbbrvSel = 'H' AND Hscore + Tot < Vscore THEN 'Loss'
WHEN AbbrvSel = 'V' AND Vscore + Tot < Hscore THEN 'Loss'
WHEN AbbrvSel = 'V' AND Vscore + Tot > Hscore THEN 'Win'
ELSE '0' END AS Result
FROM DTBackup WHERE Sport = 'NFL' AND Type = 'Side'
JOIN ConStatus
ON DTBackup.Player = ConStatus.Player
where ConStatus.Stat = '1'

INSERT INTO FFTable (Player,Sport,Game,Type,GameDate,Pick,Units,UE,Result)
SELECT Player,Sport,Game,Type,GTime,Selection,Units,
CASE WHEN AbbrvSel = 'O' AND Hscore + Vscore > Tot
THEN Units
WHEN AbbrvSel = 'O' AND Hscore + Vscore < Tot
THEN -1.1 * Units
WHEN AbbrvSel = 'U' AND Hscore + Vscore > Tot
THEN -1.1 * Units
WHEN AbbrvSel = 'U' AND Hscore + Vscore < Tot
THEN Units
ELSE 0 END AS UE,
CASE WHEN AbbrvSel = 'O' AND Hscore + Vscore > Tot THEN 'Win'
WHEN AbbrvSel = 'O' AND Hscore + Vscore < Tot THEN 'Loss'
WHEN AbbrvSel = 'U' AND Hscore + Vscore > Tot THEN 'Loss'
WHEN AbbrvSel = 'U' AND Hscore + Vscore < Tot THEN 'Win'
ELSE 'Push' END AS Result
FROM DTBackup WHERE Sport = 'NFL' AND Type = 'O/U'
JOIN ConStatus
ON DTBackup.Player = ConStatus.Player
where ConStatus.Stat = '1'

I tried the above. Not too familiar with joins. It gives me a syntax error at both of the join statements. Excuse my ignorance here. Like I said, I rarely do anything with db, as you can tell. You guys that work with it all the time have my utmost respect. It's a different animal.
Thanks.

X002548
Not Just a Number

15586 Posts

Posted - 2003-09-29 : 11:09:32

Close...you've got mutiple where clauses..


      FROM DTBackup 
INNER JOIN ConStatus
        ON DTBackup.Player = ConStatus.Player
     WHERE Sport = 'NFL' AND Type = 'Side' AND ConStatus.Stat = '1'

Brett

8-)

SELECT @@POST FROM Brain ORDER BY NewId()

That's correct! It's an AlphaNumeric!

JoeGonz
Starting Member

10 Posts

Posted - 2003-09-29 : 11:24:00

Thanks Brett but now I get two ambiguous column names at Player. Sorry for the problem.

JoeGonz
Starting Member

10 Posts

Posted - 2003-09-29 : 11:34:47

Figured it and it works. I appreciate the help, sir.

X002548
Not Just a Number

15586 Posts

Posted - 2003-09-29 : 11:47:23

Hey congrats...

You say you don't use a databse much...

I would suggest ALL logic be in stored procedures...and use whatever language you're using a a pure presentation layer...

can you feel the POWER!

PS Why do the Jets suck?

Brett

8-)

SELECT @@POST FROM Brain ORDER BY NewId()

That's correct! It's an AlphaNumeric!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources