asked on

iterate through temporary table ..

Dear Experts,

I am new to db queries.

I have a situation where i have to iterate through each row of a temp table in sybase and update another table based on the temptables value.

I am able to do this through cursors (fetch the values of temptable and update the second table), but it is taking too long to execute.

Is there a way that i can iterate through the temptable using a while loop or similar.

scenario:

declare cursor temp_cur for
select colA, colB, colC from tableA where colA = 'something'

open cursor temp_cur

fetch into .. .

if @@sqlstatus = 0
begin
<i have some statements for every value of colA here>
end

can i just loop through the temp table instead ?

something like

select cola, colb, colC into #temptable1 where colA = 'something'

while #temptable1.eof .. :) (plz forgive this poor ASP programmer)

If yes, how do i do it ?

thanks,

Jan Franek

Well, it quite depends, what kind of statements are in <i have some statements for every value of colA here>. May be it can be done in one or several SQL statements, may be not. Can you specify these statements or explain required function of the code ?

Sadagopank

ASKER

thanks jan for the response,

the statements inside the begin .. end of the fetch are something like this

fetch temp_cur into @no

if @@sqlstatus = 0
begin
select col1, col2, col3 into #temptable1 from tableA, tableb where tableA.id= tableB.id and tableA.no = @no

update #temptable5 set col1=(select colA from #temptable1 where bandwidth='ds1') where no=@no
update #temptable5 set col2=(select colB from #temptable1 where bandwidth='ds2') where no=@no
update #temptable5 set col3=(select colC from #temptable1 where bandwidth='ds3') where no=@no
end

thanks

Jan Franek

Well, it's a little confusing

In the original problem you wrote, that you declare your cursor this way:

declare cursor temp_cur for
select colA, colB, colC from tableA where colA = 'something'

And now you do fetch temp_cur into @no ? And where did bandwith column in table #temptable1 come from ? From your code it seems like #temptable1 has columns col1, col2 and col3.

I think that you probably don't need cursor at all, but it's really hard to guess what you are trying to do from pieces of code you posted. Can you post complete code that works for you (but is slow) ?

Sadagopank

ASKER

jan, here is the entire procedure. I might be doing something wrong in there using so many update statements. feed back is greatly appreciated.

CREATE PROC someproc(@type varchar(20))
as

declare equip_crs cursor for
select ee.equip_name EQUIPMENT,
ee.equip_no No,
model MODEL,
equip_clli CLLI
from equip ee,
equip_type eet
where ee.equip_type = eet.equip_type
and equip_class = @type
and ee.equip_status_code != 'di'
order by equip_name
for read only

go

create table #temptable2
(no int, Equipment varchar(30), Model varchar(30), CLLI varchar(30), ds1wired int null, ds3wired int null, oc12wired int null, oc3wired int null,ds1equipped int null, ds3equipped int null, oc12equipped int null, oc3equipped int null,
ds1inuse int null, ds3inuse int null, oc12inuse int null, oc3inuse int null,
ds1fill numeric(4,0) null, ds3fill numeric(4,0) null, oc12fill numeric(4,0), oc3fill numeric(4,0))

declare @equipment varchar(20),
@no int,
@model varchar(20),
@clli varchar(20),
@proc_err int

open equip_crs
fetch equip_crs into @equipment,
@no,
@model,
@clli

while @@sqlstatus = 0
begin

      select ee.top_level_equip,ept.bandwidth,num_items,(count(*)*num_items) Cnt into #temptable1 from card ec,
equip ee,port_templ_group eptg,port_template ept,slot ees
where ec.equip_no=ee.equip_no
and eptg.id=ec.port_templ_group_id
and ept.id=eptg.port_templ_id
and ee.top_level_equip = @no
            and ec.port_templ_group_id!=null
and ec.card_status_code!='di'
and ec.card_no=ees.card_id
and ees.work_or_prot='w'
and ept.logical_flag='n'
group by ee.top_level_equip,ept.bandwidth,num_items

insert into #temptable2 (no,Equipment, Model, CLLI) values(@no,@equipment,@model,@clli)

update #temptable2 set ds1wired=(select sum(cnt) from #temptable1 where bandwidth='ds1'),
ds3wired=(select sum(cnt) from #temptable1 where bandwidth='ds3'),
oc12wired=(select sum(cnt) from #temptable1 where bandwidth='oc12'),
oc3wired=(select sum(cnt) from #temptable1 where bandwidth='oc3')
where no=@no

drop table #temptable1

       select ee.top_level_equip,ept.bandwidth,num_items,(count(*)*num_items) Cnt into #temptable3 from card ec,
equip ee,port_templ_group eptg,port_template ept,slot ees
where ec.equip_no=ee.equip_no
and eptg.id=ec.port_templ_group_id
and ept.id=eptg.port_templ_id
and ee.top_level_equip = @no
            and ec.port_templ_group_id!=null
and ec.card_status_code in ('is','pe')
and ec.card_no=ees.card_id
and ees.work_or_prot='w'
and ept.logical_flag='n'
group by ee.top_level_equip,ept.bandwidth,num_items

update #temptable2 set ds1equipped=(select sum(cnt) from #temptable3 where bandwidth='ds1'),
ds3equipped=(select sum(cnt) from #temptable3 where bandwidth='ds3'),
oc12equipped=(select sum(cnt) from #temptable3 where bandwidth='oc12'),
oc3equipped=(select sum(cnt) from #temptable3 where bandwidth='oc3')
where no=@no

drop table #temptable3

select ee.top_level_equip,ept.bandwidth,num_items,(count(*)*num_items) Cnt into #temptable4 from card ec,
equip ee,port_templ_group eptg,port_template ept,slot ees
where ec.equip_no=ee.equip_no
and eptg.id=ec.port_templ_group_id
and ept.id=eptg.port_templ_id
and ee.top_level_equip = @no
            and ec.port_templ_group_id!=null
and ec.card_status_code in ('is','pe')
and ec.card_no=ees.card_id
and ees.work_or_prot='w'
and ept.logical_flag='n'
and ec.card_no in
            (select card_no
                  from eon_port
                  where current_path_no is not null )
group by ee.top_level_equip,ept.bandwidth,num_items

update #temptable2 set ds1inuse=(select sum(cnt) from #temptable4 where bandwidth='ds1'),
ds3inuse=(select sum(cnt) from #temptable4 where bandwidth='ds3'),
oc12inuse=(select sum(cnt) from #temptable4 where bandwidth='oc12'),
oc3inuse=(select sum(cnt) from #temptable4 where bandwidth='oc3')
where no=@no

drop table #temptable4

fetch equip_crs into @equipment, @no, @model, @clli

end

select * from #temptable2

close equip_crs
deallocate cursor equip_crs

drop table #temptable2

return

go

ASKER CERTIFIED SOLUTION

grant300

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Sadagopank

ASKER

thanks jan and grant !

grant you put me in the right track, yeah i did it with your temp tables advise. much faster :)