Results 1 to 4 of 4
  1. #1
    Join Date
    Apr 2010
    Posts
    8

    Unanswered: Problem with fetch and cursor

    Hi,

    For the past two days, I was trying to manipulate the results from test table to test_temp table using the fetch and cursor but can't get the results and hit a lot of errors. Please find the tables and my sql below. The rule here is; if there is a NULL value in subgroup column for SMEAST unit and BASE data_view, it will add additional two more rows and append SWHGRP and SMASGRP in subgroup.

    test table
    Code:
    organization  data_view	  subgroup   amount
    EAGLE	      INTERCO     SMASGRP    10
    EAGLE	      OWNER	  SWHGRP     15
    SMEAST	      BASE	  NULL       5
    SMEAST	      OWNER	  SWHGRP     18
    test_temp table
    Code:
    organization  data_view	  subgroup   amount
    EAGLE	      INTERCO     SMASGRP    10
    EAGLE	      OWNER	  SWHGRP     15
    SMEAST	      BASE	  NULL       5
    SMEAST	      BASE	  SWHGRP     10
    SMEAST	      BASE	  SMASGRP    5
    SMEAST	      OWNER	  SWHGRP     18
    The sql code:
    Code:
    declare 
    @organization varchar(16), 
    @data_view varchar(16),
    @subgroup varchar(16),
    @i int
    set @i=0
    
    DECLARE org_cursor CURSOR FOR
    select organization, data_view, subgroup from test
    open org_cursor
    fetch next from org_cursor 
    into @organization, @data_view, @subgroup
    
    while @@fetch_status = 0
    begin
    set @i=@i+1
    if @i=1 
    begin
    exec('
    drop table test_temp
    if @organization = ''SMEAST'' and @data_view = ''BASE'' and @subgroup is NULL
    begin
    SELECT organization, data_view, ''SWHGRP'' as subgroup, amount*2 as amount
    into test_temp
    FROM test
    insert into test_temp
    SELECT organization, data_view, ''SMASGRP'' as subgroup, amount
    FROM test
    end
    else
    begin
    SELECT  organization, data_view, subgroup, amount
    into test_temp
    FROM test
    end
    ')
    end
    else
    begin
    if @i>1 
    exec('
    
    if @organization = ''SMEAST'' and @data_view = ''BASE'' and @subgroup is NULL
    begin
    insert into test_temp
    SELECT organization, data_view, ''SWHGRP'' as subgroup, amount*2 as amount
    FROM test
    insert into test_temp
    SELECT organization, data_view, ''SMASGRP'' as subgroup, amount
    FROM test
    end
    else
    begin
    insert into test_temp
    SELECT organization, data_view, subgroup, amount
    FROM test
    end
    ')
    end
    
    fetch next from org_cursor
    into @organization, @data_view, @subgroup
    end
    close org_cursor
    deallocate org_cursor
    
    select * from test_temp
    the error:
    Code:
    Msg 137, Level 15, State 2, Line 3
    Must declare the scalar variable "@organization".
    Msg 156, Level 15, State 1, Line 12
    Incorrect syntax near the keyword 'else'.
    Msg 137, Level 15, State 2, Line 3
    Must declare the scalar variable "@organization".
    Msg 156, Level 15, State 1, Line 12
    Incorrect syntax near the keyword 'else'.
    Msg 137, Level 15, State 2, Line 3
    Must declare the scalar variable "@organization".
    Msg 156, Level 15, State 1, Line 12
    Incorrect syntax near the keyword 'else'.
    Msg 137, Level 15, State 2, Line 3
    Must declare the scalar variable "@organization".
    Msg 156, Level 15, State 1, Line 12
    Incorrect syntax near the keyword 'else'.
    Msg 137, Level 15, State 2, Line 3
    Must declare the scalar variable "@organization".
    Msg 156, Level 15, State 1, Line 12
    Incorrect syntax near the keyword 'else'.
    Hope someone here can enlighten me. Thanks!

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by yingchai View Post
    Hope someone here can enlighten me. Thanks!
    for starters, that isn't ANSI SQL
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    This is Transact-SQL, so it is either a Microsoft or a Sybase database engine. For now, I'll move it to Microsoft SQL and will move it again if yingchai asks me to move it.

    I'll take a stab at resolving the issue, but that will be a task for a few hours from now when I'm not working.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  4. #4
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    Try
    Code:
    CREATE TABLE #test_temp(
    	organization	varchar(16), 
    	data_view	varchar(16),
    	subgroup	varchar(16),
    	Amount		INT
    )
    
    INSERT INTO #test_temp(organization, data_view, subgroup, Amount)
    SELECT organization, 
    	data_view, 
    	'SWHGRP' as subgroup, 
    	Amount* 2 as Amount
    FROM Test
    WHERE organization = 'SMEAST' and 
    	data_view = 'BASE' and 
    	subgroup is NULL
    
    UNION ALL
    
    SELECT organization, 
    	data_view, 
    	'SMASGRP' as subgroup, 
    	Amount
    FROM Test
    WHERE organization = 'SMEAST' and 
    	data_view = 'BASE' and 
    	subgroup is NULL
    
    UNION ALL
    
    SELECT organization, 
    	data_view, 
    	subgroup, 
    	Amount
    FROM Test
    WHERE NOT (organization = 'SMEAST' and 
    	data_view = 'BASE' and 
    	subgroup is NULL)
    It might run a tiny bit faster than the cursor solution.
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

Posting Permissions

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