Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 2 of 2
Thread: SQL Cursor help
04-15-2009, 10:30 PM #1
- Join Date
- Nov 2008
- Thanked 0 Times in 0 Posts
SQL Cursor help
I have the following table that i want to update.. and get values from..
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
@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
Last edited by TechGlider; 04-15-2009 at 10:49 PM.
04-17-2009, 03:52 AM #2
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
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
So... Are you showing us a too-much-simplified version of the actual problem?? It would seem so, to me.