If Else puzzle - 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)

If Else puzzle

Author

Topic

John T.
Posting Yak Master

112 Posts

Posted - 2003-04-23 : 20:28:21

Select * From DailyTable Where Sport = 'MLB' AND Type = 'Game'
If Selection = 'H' AND Hscore > Vscore
Begin
If No > 0
Begin
Set @ue = Un * No/100,@result = 'Win'
Else
Set @ue = Un, @result = 'Win'
End
Else If
Selection = 'H' AND Hscore < Vscore
Begin
If No > 0
Set @ue = -Un,@result = 'Loss'
Else
Set @ue = Un * No/100,@result = 'Loss'
End
Else If
''''Am I on the right track? My guess is there is another train coming. I get errors for the comma before @result. But I am not certain at all about the begin and ends here.
Thanks.

nr
SQLTeam MVY

12543 Posts

Posted - 2003-04-23 : 22:04:32

You don't need and of the begin/end blocks as you only have single statements in the blocks. If you have them they canot span the elses.
Always putting in a begin end can be a good idea for formatting.

If No > 0
Begin
Set @ue = Un * No/100,@result = 'Win'
Else
Set @ue = Un, @result = 'Win'
End

is invalid - change to

If No > 0
Begin
Set @ue = Un * No/100,@result = 'Win'
End
Else
Begin
Set @ue = Un, @result = 'Win'
End

you cannot set two variable in a single set command

If No > 0
Begin
Set @ue = Un * No/100
Set @result = 'Win'
End
Else
Begin
Set @ue = Un
Set @result = 'Win'
End

(needs the begin end) or

If No > 0
Begin
Select @ue = Un * No/100,@result = 'Win'
End
Else
Begin
Select @ue = Un, @result = 'Win'
End

so

If Selection = 'H' AND Hscore > Vscore
Begin
If No > 0
Begin
Set @ue = Un * No/100
Set @result = 'Win'
End
Else
Begin
Set @ue = Un
Set @result = 'Win'
End
Else If Selection = 'H' AND Hscore < Vscore
Begin
If No > 0
Begin
Set @ue = -Un
Set @result = 'Loss'
End
Else
Begin
Set @ue = Un * No/100
Set @result = 'Loss'
End
Else If ......

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

John T.
Posting Yak Master

112 Posts

Posted - 2003-04-24 : 00:32:34

I have been looking at this for quite awhile. First, thanks. But I am confused on the very first BEGIN in your final example.

If Selection = 'H' AND Hscore > Vscore
Begin
If No > 0
Begin
Set @ue = Un * No/100
Set @result = 'Win'
End
Else

I am thinking that the first begin is not necessary? Wouldn't the If No > 0 be considered one statement, since it is a block of code?
I am having no success in getting this and went back to an old try that you probably all have done at one time or the other. I start with a simple block of code and keep trying to add to it. I reduced the code to simply :
If selection = 'H'
Begin
Set @ue = un * no / 100
Set @result = 'Win'
End
Else
Set @result = 'Win'
End
Go
I get the same "incorrect syntax near the keyword 'END' error that I get with the entire block of code I have been trying. I remove the END. Add an END. Try () around column names. Everything but the right thing. I have done a little coding in other languages but this SQL is an animal of its own. And it's hard to find good searches on If.. Then or Case with Sql. Unless I am doing that wrong also.

nr
SQLTeam MVY

12543 Posts

Posted - 2003-04-24 : 01:53:02

The Begin needs an end -
If Selection = 'H' AND Hscore > Vscore
Begin
If No > 0
Begin
Set @ue = Un * No/100
Set @result = 'Win'
End
Else
begin
...
end
end

If selection = 'H'
Begin
Set @ue = un * no / 100
Set @result = 'Win'
End
Else
Begin
Set @result = 'Win'
End
Go

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-04-24 : 10:30:00

Try to think of it this way:

The syntax of the IF-ELSE statement is:

IF {condition} {TrueStatement} ELSE {FalseStatement}

You can techinically only have 1 statement in each part of the IF; one for the true part, one for the false part.

BUT .... anywhere SQL expects a single statement, you can instead put a group of statements surround by a BEGIN and an END.

This is true for a WHILE loop as well ... the syntax is

WHILE {Condition} {Statement}

But you can't do much with 1 statement ... so use a list of statements, surrounded by a BEGIN and an END instead.

Summing up,

IF @A=1 SET @A=2 ELSE SET @A=3

is the same as

IF @A=1
BEGIN
SET @A=2
END
ELSE
BEGIN
SET @A=3
END

and for loops,

WHILE @A<10 SET @A = @A + 1

is the same as

WHILE @A<10
BEGIN
SET @A = @A + 1
END

The difference in both cases being: if you use a BEGIN and an END (and they must always match up) you can put group together more than 1 statement for a condition or a loop.

One last thing: remember that a long IF-ELSE statement, even though it contains many parts, is still 1 statement. Thus:

IF @A < 10
IF @B < 10
SET @A = 12
ELSE
SET @B = 2
ELSE
SET @C = 3

is perfectly legal as well, and there is still no need for BEGIN's or END's because there is never more than 1 statement grouped together.

Hope this helps.

- Jeff

Edited by - jsmith8858 on 04/24/2003 10:31:42

John T.
Posting Yak Master

112 Posts

Posted - 2003-04-24 : 10:39:08

Maybe just maybe I can get an education with this.
Select Name,Gtime,Game,Spt,Type,Sel,Os,Un,Vs,Hs,Abb From DT Where Spt = 'MLB' AND Type = 'Sd'

If Sel = 'H' AND Hs > Vs *** If #1
Begin *** Begin #1
If Os > 0 ***If #2
Begin *** Begin #2
Set @ue = 32
Set @res = 54
End *** End #1(matches Begin #2)
Else
Begin *** Begin #3
Set @ue = 31
Set @res = 22
End *** End #2 (matches Begin #3)
End *** End # 3 (matches Begin #1. This is where I am probaby wrong)
Else If Sel = 'H' AND Hs < Vs *** If #3
Begin *** Begin #4
If Os > 0 ***If #4
Begin *** Begin #5
Set @ue = 32
Set @res = 54
End *** End #4(matches Begin #5)
Else
Begin *** Begin #6
Set @ue = 31
Set @res = 22
End *** End #5 (matches Begin #6)
End *** End # 6 (matches Begin #4)
Else If Sel = 'V' AND Hs < Vs *** If #4
Begin *** Begin #7
If Os > 0 ***If #5
Begin *** Begin #8
Set @ue = 32
Set @res = 54
End *** End #6(matches Begin #8)
Else
Begin *** Begin #9
Set @ue = 31
Set @res = 22
End *** End #7 (matches Begin #9)
End *** End # 8 (matches Begin #7)
Else ***
Begin *** Begin #10
If Os > 0 ***If #6
Begin *** Begin #11
Set @ue = 32
Set @res = 54
End *** End #9(matches Begin #11)
Else
Begin *** Begin #12
Set @ue = 31
Set @res = 22
End *** End #10 (matches Begin #12)
End *** End # 11 (matches Begin #10)

I apologize but couldn't think of a better way to display this. I think it is just a matter of syntax here. Or lack of intelligence on my part. Probably the latter. I am confused on where the begin..ends go. Inside the Os > 0 is easy. It's outside of them. Thanks.

X002548
Not Just a Number

15586 Posts

Posted - 2003-04-24 : 11:18:22

OK, This is my best shot (and I couldn't hit the broad side of a barn):


DECLARE @Sel Char(1), @Os Int, @Vs Int, @Hs Int
DECLARE @ue Int @res int

-- 1. Your code is not TRANSACT SQL, what is it?  Comments are identified by: /* something */, per block or -- per line
-- 2. Name is not referenced in your code, remove it from the select.  Also Name is a reserved you should change it
-- 3. GTime, Game, Spt, Type, Un, Abb Are not referenced, remove it
-- 4. Are you sure your result set will only return 1 row?  If not you'll only get the last(? Guys Help) Row back
--    I'm thinking you'll get more than one, in which you'll need a cursor (not really though)
-- 5. I'm assuming Vs and Gs are Int
-- 6. Identing the code is helpful to see it use [ code ] ...actual code [ /code ] with no space to post formatted code
-- 7.  This seems like it all could be done with a CASE staement
-- 8. Also in All cases you only assign the local variable to (32 and 54) or (31 and 22), gotta be an easier way
-- 9. This is your actual (dare I say) "Logic":
-- WHEN TRUE  (Sel=H and HS > VS and OS > 0) 32, 54
-- WHEN FALSE (Sel=H and HS > VS and OS > 0) 31 ,22
-- WHEN FALSE (Sel=H and HS > VS and OS > 0) AND TRUE (Sel=H and HS < VS and Os > 0) 32, 54
-- WHEN FALSE (FALSE(Sel=H and HS > VS and OS > 0) AND TRUE (Sel=H and HS < VS and Os > 0)) 31,22
-- still more but....
-- 10. I gave up on a solution because my brain hurts...you must be able to define your criteria in business terms

SELECT 
	--Name,
	--Gtime,
	--Game,
	--Spt,
	--Type,
	  @Sel = Sel
	, @Os = Os
	--,Un,
	, Vs
	, Hs
	--,Abb 
  FROM   DT 
 WHERE  Spt = 'MLB' AND Type = 'Sd' 

If Sel = 'H' AND Hs > Vs *** If #1 
	Begin *** Begin #1 
		If Os > 0 ***If #2 
			Begin *** Begin #2 
				Set @ue = 32 
				Set @res = 54 
			End *** End #1(matches Begin #2) 

		Else 
			Begin *** Begin #3 
				Set @ue = 31 
				Set @res = 22 
			End *** End #2 (matches Begin #3) 
	End *** End # 3 (matches Begin #1. This is where I am probaby wrong) 
Else If Sel = 'H' AND Hs < Vs *** If #3 
	Begin *** Begin #4 
		If Os > 0 ***If #4 
			Begin *** Begin #5 
				Set @ue = 32 
				Set @res = 54 
			End *** End #4(matches Begin #5) 
		Else 
			Begin *** Begin #6 
				Set @ue = 31 
				Set @res = 22 
			End *** End #5 (matches Begin #6) 
	End *** End # 6 (matches Begin #4) 
Else If Sel = 'V' AND Hs < Vs *** If #4 
	Begin *** Begin #7 
		If Os > 0 ***If #5 
			Begin *** Begin #8 
				Set @ue = 32 
				Set @res = 54 
			End *** End #6(matches Begin #8) 
		Else 
			Begin *** Begin #9 
				Set @ue = 31 
				Set @res = 22 
			End *** End #7 (matches Begin #9) 
	End *** End # 8 (matches Begin #7) 
Else *** 
	Begin *** Begin #10 
		If Os > 0 ***If #6 
			Begin *** Begin #11 
				Set @ue = 32 
				Set @res = 54 
			End *** End #9(matches Begin #11) 
		Else 
			Begin *** Begin #12 
				Set @ue = 31 
				Set @res = 22 
			End *** End #10 (matches Begin #12) 
	End *** End # 11 (matches Begin #10)

Sorry, you got me beat.

Brett

8-)

Edited by - x002548 on 04/24/2003 11:21:23

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-04-24 : 11:24:29

John -- do me a favor. Write in plain old english what you are trying to ... but be very detailed.

- Jeff

John T.
Posting Yak Master

112 Posts

Posted - 2003-04-24 : 11:31:37

Sorry for the confusion. I should have never put all that junk up. The set statements are just garbage to fill in the blocks. They don't matter. Save time just letting the sets be short.
This program is supposed to run through a table and get all baseball(MLB) selections that involved a fantasy play on the game itself.
Then I break it down to four scenarios. H and Hscore > Vscore means he had the Home team and won. H and Hscore < Vscore means he had home team and lost. Reverse it when the record shows a V as selection.
Then I determine with the Os greater than zero how many units the guy won or lost. Don't want to get into that because it isn't important for this. Os > 0 means it was an underdog and < 0 was a favorite.
After I determine a winner and how many points he wins, it gets written to a table.
Problem for me is the begin and ends. Inside the outer If statements.
Sorry to bother anyone. Ignoring this is fine.

John T.
Posting Yak Master

112 Posts

Posted - 2003-04-24 : 11:49:36

Make another stab at unembarrassing myself here.
Select Player,Gtime,Game,Sp,Type,Sel,Os,Un,Vs,Hs,Abb From DT Where Sp = 'MLB' and Type = "Sd"
The above select just gets the fields I want from my game selection table. DT.

If Sel = 'H' and Hs > Vs Simply means this record has a winner and the following If Os > 0 is how I determine how many points the winner gets.
All the blocks of code inside of If Os > 0 are the easy parts.

For each If Sel = 'H' and Hs > Vs type blocks, there is my problem. Where the matching begin ends go.

If Sel = 'H' and Hs > Vs
Begin /* is there a begin here?
If Os > O /* this part no problem
End /* is there an end here?
Else If Sel = 'H' and Hs < Vs
Begin /* not sure of begins and ends outside of If Os > 0 blocks
If Os > 0 /* Once again, no problem in this part.
.........
.........
Else If Sel = 'V' and Hs < Vs
If Os > 0
End /* If I can just match the begin ends for the If sel = 'H' and Hs > Vs type blocks, I am fine. I will get a text for this code. I do have a SQL for dummies book(appropriate) and an Sql server 7 book(given to me). But I am using 2000. And these books do little on If..thens and cases. Or I wouldn't be doing this. What I want to do is so simple but I just can't get the syntax.
sorry for any inconvenience.

X002548
Not Just a Number

15586 Posts

Posted - 2003-04-24 : 12:04:33

OK, I'll give it ONE more try to explain this.

DO NOT DO WHAT YOURE DOING

Do this:


SELECT CASE WHEN Sel=H and HS > VS and OS > 0    OR
	         (some other Positive condition) OR
	         (some other Positive condition) OR
	         (some other Positive condition) OR
	         (some other Positive condition) OR
	         (some other Positive condition) THEN 32
	    WHEN (some other Positive condition) OR
	         (some other Positive condition) OR
	         (some other Positive condition) OR
	         (some other Positive condition) OR
	         (some other Positive condition) THEN 31
	END
CASE WHEN Sel=H and HS > VS and OS > 0    OR
	         (some other Positive condition) OR
	         (some other Positive condition) OR
	         (some other Positive condition) OR
	         (some other Positive condition) OR
	         (some other Positive condition) THEN 54
	    WHEN (some other Positive condition) OR
	         (some other Positive condition) OR
	         (some other Positive condition) OR
	         (some other Positive condition) OR
	         (some other Positive condition) THEN 22
	END

Thats about the best I can do for you.

Do not write spagehtti code

Brett

8-)

John T.
Posting Yak Master

112 Posts

Posted - 2003-04-24 : 15:31:54

Select Case When sel = 'H' And Hscore > Vscore and os > 0
Then
Set @ue = un * os
Set @result = 'Win'
End
Case When sel = 'H' and Hscore > Vscore and os < 0
Then
Set @ue = un
Set @result = 'Win'
End
Case When sel = 'H' and Hscore < Vscore and os > 0
Then
Set @ue = un
Set @result = 'Loss'
End
Case When sel = 'H' and Hscore < Vscore and os < 0
Then
Set @ue = un * os
Set @result = 'Loss'
End

Now I have tried this code with Begin and Ends surrounding the Set statements. Also, not sure how to handle the last case statement. I appreciate the help and can surely handle the critcism. Only been doing this a short period of time. I know one thing. If I could use vb.net, pascal, or just about anything but SQL, I wouldn't have a problem with this. At least I could refer to the syntax and make it work.
Once again, thanks. The help is great.

X002548
Not Just a Number

15586 Posts

Posted - 2003-04-24 : 15:52:34

This is the syntax. It matches the logic in your last post:


SELECT 	  @ue =     CASE WHEN sel = 'H' And Hscore > Vscore and os > 0 Then un * os
		         WHEN sel = 'H' and Hscore > Vscore and os < 0 OR
		              sel = 'H' and Hscore < Vscore and os > 0 OR
		              sel = 'H' and Hscore < Vscore and os < 0 Then un
								       ELSE 0
	            END
	, @Result = CASE WHEN sel = 'H' And Hscore > Vscore and os > 0 
		  	      sel = 'H' and Hscore > Vscore and os < 0 THEN 'Win'
		         WHEN sel = 'H' and Hscore < Vscore and os > 0 OR
		              sel = 'H' and Hscore < Vscore and os < 0 Then 'Loss'
								       ELSE 'unknown'
		    END
  FROM DT 
WHERE Sp = 'MLB' AND Type = 'Sd'

Brett

8-)

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-04-24 : 15:55:28

OK ... I think your last post clears it up for me.

type this in, examine it, run this and tell me what you get:

Select Name,Gtime,Game,Spt,Type,Sel,Os,Un,Vs,Hs,Abb,
Case When sel='H' and os > 0 Then un * os else un End as UE,
CASE WHEN sel='H' and HScore > VScore Then 'Win' Else 'Loss' END as Result
From DT
Where Spt = 'MLB' AND Type = 'Sd'

Hopefully that will give you some ideas ... you can process the entire table at once and come up with your UE and Result values using CASE expressions for ALL ROWS in the table all at the same time, as extra calculated fields in your query.

For example, if you had this query:

Select ID, Units, UnitPrice
From data

you would not have to go one by one and use IF THEN's and SET variables to find out the total price for each ID, you can do it all at once:

Select ID, Units, UnitPrice, Units * UnitPrice as TotalPrice
FROM data

and if it was more complex, you can use a CASE statement as well:

Select ID, Units, UnitPrice, BoughtOrSold,
CASE WHEN BoughtOrSold = 'Bought' THEN Units * UnitPrice ELSE -1 * Units * UnitPrice END as TotalPrice
FROM data

I hope this helps clear some things up and gives you a guideline on how to get to where you need to be.

- Jeff

Edited by - jsmith8858 on 04/24/2003 16:19:35

John T.
Posting Yak Master

112 Posts

Posted - 2003-04-24 : 16:41:23

CREATE PROCEDURE MProc AS
DECLARE @res VarChar(50),@ue DEC
SELECT player,gtime, game,sp,type,sel,os,un,Vscore,Hscore,Abb,
@ue CASE WHEN sel = 'H' AND Hscore > Vscore AND os > 0
THEN un * os/100
WHEN sel = 'H' AND Hscore > Vscore AND os < 0
THEN un
WHEN sel = 'H' AND Hscore < Vscore AND os > 0
THEN -un
WHEN sel = 'H' AND Hscore < Vscore AND os < 0
THEN un * os/100
ELSE 0
END
@ue CASE WHEN sel = 'V' AND Hscore > Vscore AND os > 0
THEN un -un
WHEN sel = 'V' AND Hscore > Vscore AND os < 0
THEN un * os
WHEN sel = 'V' AND Hscore < Vscore AND os > 0
THEN un * os/100
WHEN sel = 'V' AND Hscore < Vscore AND os < 0
THEN un
ELSE 0
END
@res CASE WHEN sel = 'H' AND Hscore > Vscore AND os > 0
THEN 'Win'
WHEN sel = 'H' AND Hscore > Vscore AND os < 0
THEN 'Win'
WHEN sel = 'H' AND Hscore < Vscore AND os > 0
THEN 'Loss'
WHEN sel = 'H' AND Hscore < Vscore AND os < 0
THEN 'Loss'
ELSE '0'
END
@res CASE WHEN sel = 'V' AND Hscore > Vscore AND os > 0
THEN 'Loss'
WHEN sel = 'V' AND Hscore > Vscore AND os < 0
THEN 'Loss'
WHEN sel = 'V' AND Hscore < Vscore AND os > 0
THEN 'Win'
WHEN sel = 'V' AND Hscore < Vscore AND os < 0
THEN 'Win'
ELSE '0'
END
FROM DT WHERE sp = 'MLB' AND type = 'Sd'
GO

First, I appreciate this help. I am learning here. Duh. I still get a syntax error at line 5...CASE. What I am doing is this. I am getting the record info from the DT table. You see that. When I determine if it is a win or loss, I will insert the fields to another table. It will be a table kept in the db for users to look and see how each guy is doing.
Once again, thanks for this help. I am truly grateful. I actually love this stuff. Wish I would have started it, well, I would show my age. What the heck, 30 years ago.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-04-24 : 17:05:58

John --

Get rid of the variables. you DON't need them. read the example SQL statements I gave you -- they might seem like they don't apply to your situation, but they do. Play around with SELECT statements, CASE statements, and creating your own expressions. start small. get it to work in 1 single case, then add another. get it to work with 2, then add another. Start with simple things is the best advice I can give.

here at work, people ask me to help them with complex joins and expressions and stuff in Access, using existing data with 1000s of records with 100s of fields and all different conditions. I tell them to close it all down, create a new database, create 2 tables with like 5 rows each, and work on all the concepts that they need until they completely understand ramifications, syntax, etc. That's definitely what I recommend in your case -- like my simple Units * UnitPrice examples.

Having said all that, when you are done, and confident, and understand how to use CASE statemnts and alias's and how to build expressions into a SELECT statement, look at the modifications I have made:

SELECT player,gtime, game,sp,type,sel,os,un,Vscore,Hscore,Abb,
CASE WHEN sel = 'H' AND Hscore > Vscore AND os > 0
THEN un * os/100
WHEN sel = 'H' AND Hscore > Vscore AND os < 0
THEN un
WHEN sel = 'H' AND Hscore < Vscore AND os > 0
THEN -un
WHEN sel = 'H' AND Hscore < Vscore AND os < 0
THEN un * os/100
WHEN sel = 'V' AND Hscore > Vscore AND os > 0
THEN un -un
WHEN sel = 'V' AND Hscore > Vscore AND os < 0
THEN un * os
WHEN sel = 'V' AND Hscore < Vscore AND os > 0
THEN un * os/100
WHEN sel = 'V' AND Hscore < Vscore AND os < 0
THEN un
ELSE 0 END AS UE,
CASE WHEN sel = 'H' AND Hscore > Vscore THEN 'Win'
WHEN sel = 'H' AND Hscore < Vscore THEN 'Loss'
WHEN sel = 'V' AND Hscore > Vscore THEN 'Loss'
WHEN sel = 'V' AND Hscore < Vscore THEN 'Win'
END AS Result
FROM DT WHERE sp = 'MLB' AND type = 'Sd'

don't try to create stored procedures or anything like -- try to get a simple SELECT to work, add 1 part at a time. First try to calc the "Result" field -- you will note that you don't care at all about the "os" value, right? Then figure out your UE field.

Then, when you can SELECT all the data you need, you can INSERT it all at once -- without decalring variables or using IF-THEN's into another table like this:

INSERT INTO anothertable (field1, field2, field3)
SELECT field1,field2,field3
FROM
.... whatever you want ....

But as you will learn from working with SQL, you probably DON'T need to store it in another table, because a SELECT statement or a VIEW will let you view the data in almost any format you need -- without needing to copy it to another place.

Once you copy the data to another table, you have to remember that something needs to be run to peform this copy. How often should it be run? who should do it? should it only add NEW data to this table, or completely overwrite it each time? etc ...

Good luck ... please, start small, keep it simple, 1 step at a time.

- Jeff

Edited by - jsmith8858 on 04/24/2003 17:08:44

John T.
Posting Yak Master

112 Posts

Posted - 2003-04-24 : 19:10:11

Thank you very much. Appreciated so much.

John T.
Posting Yak Master

112 Posts

Posted - 2003-04-24 : 22:59:26

INSERT INTO Universal (player,sp,game,type,gdate,pk,un,res,ue) VALUES
(player,sp,game,type,gtime,sel,un,res,ue)
SELECT player,gtime, game,sp,type,sel,os,un,Vscore,Hscore,Abb,
CASE WHEN sel = 'H' AND Hscore > Vscore AND os > 0
THEN un * os/100
WHEN sel = 'H' AND Hscore > Vscore AND os < 0
THEN un
WHEN sel = 'H' AND Hscore < Vscore AND os > 0
THEN -un
WHEN sel = 'H' AND Hscore < Vscore AND os < 0
THEN un * os/100
WHEN sel = 'V' AND Hscore > Vscore AND os > 0
THEN un -un
WHEN sel = 'V' AND Hscore > Vscore AND os < 0
THEN un * os
WHEN sel = 'V' AND Hscore < Vscore AND os > 0
THEN un * os/100
WHEN sel = 'V' AND Hscore < Vscore AND os < 0
THEN un
ELSE 0 END AS UE,
CASE WHEN sel = 'H' AND Hscore > Vscore THEN 'Win'
WHEN sel = 'H' AND Hscore < Vscore THEN 'Loss'
WHEN sel = 'V' AND Hscore > Vscore THEN 'Loss'
WHEN sel = 'V' AND Hscore < Vscore THEN 'Win'
END AS Result
FROM DT WHERE sp = 'MLB' AND type = 'Sd'

First, I have learned much from this code. I understand that I don't need to use the variables that I had before. This case thing is quite nice.
One thing I don't understand. I get an error with the first field in the VALUES(player,sp,game,type,gtime,sel,un,res,ue). "The name 'player' is not permitted in this context. Only constants, expressions, or variables allowed here. Column names are not permitted."
Now I ran this in QA, without any other code. Just the INSERT INTO...VALUES..... code. Same error. I had thought that perhaps since the column names were the same for both tables, that could be my problem. So I changed the value of player in the VALUES section to something else. It still gives the same error message. Even though the new value was not a column name. Did a search on Google for that error message and really found no hint. Tried parantheses, commas, no parentheses.

nr
SQLTeam MVY

12543 Posts

Posted - 2003-04-24 : 23:19:22

The insert ... values(...) syntax is used for inserting a list of variables or literals.
To insert from a table (or variables or literals)
insert ...
select ...
from ...

So if you just drop your values clause you should be ok.

have a look in bol (books online) - it gives the syntax for all these statements.

INSERT [ INTO]
{ table_name WITH ( < table_hint_limited > [ ...n ] )
| view_name
| rowset_function_limited
}

{ [ ( column_list ) ]
{ VALUES
( { DEFAULT | NULL | expression } [ ,...n] )
| derived_table
| execute_statement
}
}
| DEFAULT VALUES

< table_hint_limited > ::=
{ FASTFIRSTROW
| HOLDLOCK
| PAGLOCK
| READCOMMITTED
| REPEATABLEREAD
| ROWLOCK
| SERIALIZABLE
| TABLOCK
| TABLOCKX
| UPDLOCK
}

the syntax you want is to insert a derived_table.
Look at the sample statements at the end.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Edited by - nr on 04/25/2003 00:11:09

John T.
Posting Yak Master

112 Posts

Posted - 2003-04-25 : 07:46:20

Again, thanks for the help. I have now learned something else. I had thought that in order to use values for comparison(like the values Hscore and Vscore) that I had to have them in the select statement. And I now know how to fill that other table from an existing table.
I am going to ask a few more questions. This thread has been like going to school for me.
In my code, I have un and os. Both are smallints. Say un = 2 and os = 180. Multiplying the two and get 360. Divide by 100 yields 3.60. In my code, I do CONVERT(DEC,un) * CONVERT(DEC,os). Now ue's column type is DEC and set to 2 decimal places. I am wondering what I am doing wrong here as all of my values seem to be rounded to whole numbers. And rounded down. In other words, 3.60 would be just 3.
Another question is this. What I am doing is this. I am taking daily results and grading them. The graded results go to a master table. That will be there "forever". Four fields determine uniqueness. player,date,sp,and type. So I assume they are keys??
And lastly, is indexing this master table on date what I should do? All queries will be done using a particular player and date range.
Learning much here and thanks again.
John

nr
SQLTeam MVY

12543 Posts

Posted - 2003-04-25 : 09:45:29

Look at decimal datatype in bol.
you will see that the the syntax is dec(p,s) s being the number of decimal places with a default of 0. You are doing your arithmetic with 0 decimal places so it will truncate
Also try in query analyser
select convert(dec,5.7)
select 2 * 180 / 100
select convert(dec,2) * select convert(dec,180) / 100

now try
select convert(dec(18,4),2) * select convert(dec(18,4),180) / 100
and
select 1.0 * 2 * 180 / 100

The fields that identify a row should be made the primary key.

Not sure what the lastly question is.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources