Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 2 of 2

Thread: SQL Cursor help

  1. #1
    New Coder
    Join Date
    Nov 2008
    Posts
    17
    Thanks
    0
    Thanked 0 Times in 0 Posts

    SQL Cursor help

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

    table1


    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..

    Code:
    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
    Code:
    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...

    Last edited by TechGlider; 04-15-2009 at 09:49 PM.

  • #2
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,216
    Thanks
    75
    Thanked 4,344 Times in 4,310 Posts
    It's strange, to me, that you say the inner SELECT there gives you the right answer:
    Code:
    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:
    Code:
    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.


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •