SQL Cursor help

[TechGlider]

I have the following table that i want to update.. and get values from..

table1
http://www.umsl.edu/~cjgwb3/data.jpg

What i need to do is loop through this table.. one row at a time getting the value from column 'branch', and using that value, put it in the select query that will give me the results i need. Then put that result needed into column 'branch_count' in its respective row that the original value was gotten from. And move on to the next column 'branch's, row which would be row number 2 and do this until there are no more rows..

This is what i currently have but it is not working correctly..


DECLARE branchComparison
CURSOR FOR SELECT branch
FROM table1
DECLARE @e_branch integer
OPEN branchComparison
FETCH FROM branchComparison INTO @e_branch
WHILE @@FETCH_STATUS = 0
BEGIN
UPDATE table1 SET branch_count =
(SELECT
count(distinct calculatedValue)
FROM tablea a,
tableb b,
JOIN tablec c
ON b.valueX = c.valueY
WHERE b.valueZ > '01/01/2000'
AND a.branch = @e_branch
AND b.valueC = 1
AND c.valueD IS NOT NULL)
FETCH NEXT FROM branchComparison INTO @e_branch
END
CLOSE branchComparison
DEALLOCATE branchComparison


If I run the select query by itself and get the desired result buy manually replacing the
@e_branch value with the respective column and row value.. The result of

SELECT
count(distinct calculatedValue)
FROM tablea a,
tableb b,
JOIN tablec c
ON b.valueX = c.valueY
WHERE b.valueZ > '01/01/2000'
AND a.branch = @e_branch <--- Which would now be "AND a.branch = 1"
AND b.valueC = 1
AND c.valueD IS NOT NULL


Running that select query gives me this result...

http://www.umsl.edu/~cjgwb3/data2.jpg

[Old Pedant]

It's strange, to me, that you say the inner SELECT there gives you the right answer:

SELECT
count(distinct calculatedValue)
FROM tablea a,
tableb b,
JOIN tablec c
ON b.valueX = c.valueY
WHERE b.valueZ > '01/01/2000'
AND a.branch = @e_branch
AND b.valueC = 1
AND c.valueD IS NOT NULL

Because it sure looks to me like you have a syntax error.

To wit, you have a comma after "table b," and yet the next think we see is the keyword JOIN.

But what is really strange to me is that you don't join tableA to *EITHER* tableB or tableC. So I *assume* that what you are after is a *full* cartesian product of tableA with the joined tables tableB and tableC. Is that so???

What table is calculatedValue coming from???? If it is coming from tableA, then there's no point in involving tableB and tableC in the SELECT, is there???

But by the same token, if it is coming from either tableB or tableC, then there's no point in joining tableA, that I can see!

Yes, if you'd been doing a simple COUNT(calculatedValue), then the cartesian product could massively affect the count. But with COUNT(DISTINCT ...) the extra multiples produced by the cartesian product won't affect the number of *distinct* values.

In other words, if you had data such as:

tableA
branch :: calculatedValue
1 :: xxx
1 :: yyy
2 :: zzz

Then COUNT(distinct calculatedValue) WHERE a.branch=1 is going to be 2, no matter what the results of the join of B and C are.

So... Are you showing us a too-much-simplified version of the actual problem?? It would seem so, to me.