case and pointers

[keizersoz]

Hello to all,

I have two questions.
Question number 1: I have a problem using the "case" operator. Initially I wrote 6 "update" statements to be executed on different boolean conditions , and then I figured out it would be more productive to change it to one update statement using "case" . Unfortunately , after I made this changement the code doesn't work anymore and I don't know why.

The structure of the update I wrote is the following:

update Tbl_Commission
set Commission=
CASE
when com.regular_good='S'andsal.SalesrepCode=com.SalesRepNumber
then ExtPrice*sal.CommSpecialItems

when com.regular_good<>'S'and com.Steady<>'S'and Price_level=1 and sal.SalesrepCode=com.SalesRepNumber
then ExtPrice*sal.CommNonStItemsL1

...
END
from Tbl_Commission com,tblSalesrep sal

--None of the case options is ever executed , how comes? Is the case really executed on each record from Tbl_commission table?

Question 2 : How can I in sqlserver return record by record from a table . Which type of pointer/variables should I use ?

 

[vongrunt]

#1: establish JOIN between TBL_Commission and tblSalesrep
#2: search help files for "cursors

 

[donutman]

Vongrunt is saying that you failed to establish how the two tables are to be joined. "from Tbl_Commission com,tblSalesrep sal" should read something like:
from Tbl_Commission com inner join tblSalesrep sal
on com.ComKey=sal.ComKey
If you are new to SQL don't do cursors. They look attractive to programmers who commonly work with records, but they should be considered part of the axis of evil. There is almost always a more effiecient way to solve the problem without cursors. One contributor on this forum has a great signature line that is SO true. It says "Cursors are very useful if you don't know SQL."
-Karl

 

[keizersoz]

Ok this is solved instead of establishing join I did
update
....
from Tbl_Commission com,tblSalesrep sal
where sal.SalesrepCode=com.SalesRepNumber

Is this analoguous to join , what is more productive? Actually I don't understand why it didn't work before : what does it matter if I write the condition "sal.SalesrepCode=com.SalesRepNumber" in each the case or in the "where" clause besides productivity of course.