Problem with fetch and cursor - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Problem with fetch and cursor

Author

Topic

yingchai
Starting Member

33 Posts

Posted - 2011-05-22 : 09:55:06

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


organization  data_view	  subgroup   amount
EAGLE	      INTERCO     SMASGRP    10
EAGLE	      OWNER	  SWHGRP     15
SMEAST	      BASE	  NULL       5
SMEAST	      OWNER	  SWHGRP     18

test_temp table


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:


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

I am not sure if my objective can be achieved by using fetch and cursor method. Hope someone here can enlighten me. Thanks!

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-05-22 : 10:14:33

[code]
select *
from test

union all

select organization, data_view, f.subgroup, amount * factor
from test d
cross join
(
select subgroup = 'SMASGRP', factor = 1 union all
select subgroup = 'SWHGRP', factor = 2
) f
where d.organization = 'SMEAST'
and d.data_view = 'BASE'
and d.subgroup is NULL
[/code]

KH
[spoiler]Time is always against us[/spoiler]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources