Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

iterate through temporary table ..

Posted on 2004-11-01
6
Medium Priority
?
2,470 Views
Last Modified: 2012-06-22
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,

0
Comment
Question by:Sadagopank
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
6 Comments
 
LVL 14

Expert Comment

by:Jan Franek
ID: 12471002
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 ?
0
 

Author Comment

by:Sadagopank
ID: 12473732
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
0
 
LVL 14

Expert Comment

by:Jan Franek
ID: 12474029
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) ?
0
New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

 

Author Comment

by:Sadagopank
ID: 12474155
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
0
 
LVL 19

Accepted Solution

by:
grant300 earned 750 total points
ID: 12479968
You can easily do this in a single query with no cursors (which are bad anyway) and no per-record temp tables.  The trick is to structure it as a union of the three sections you have in the existing procedure and get rid of the cursor by including the equip_type table in each of the select statements as a JOIN.

I will show you the pattern for the query but I don't have time to rewrite your whole procedure for you.

Here are the things the query will do:
1) Add the EQUIP_TYPE table to each of your three SELECT statements making them JOINS
2) Add dummy columns with Zeros (and matching column names) in each query so that you have the same set of
    columns from all three queries.
3) calculate the individual columns in each section based on the bandwidth column using CASE statements
4) Connect all three queries with a UNION ALL so you get one result set back
5) Change the query (first select in the string of three) to be INTO a temporary table

So at this point you have something like
  SELECT no, equipment, model, CLLI,
              case(bandwidth = 'ds1' then ds1wired else 0 end) ds1wired,
              case(bandwidth = 'ds3' then ds3wired else 0 end) ds3wired,
              case(bandwidth = 'oc12' then oc12wired else 0 end) oc12wired,
              case(bandwidth = 'oc3' then oc3wired else 0 end) oc3wired,
              0 ds1equipped, 0 ds3equipped, 0 oc12equipped, 0 oc3equipped,
              0 ds1inuse, 0 ds3inuse, 0 oc12inuse, 0 oc3inuse
    INTO #temp1
     FROM  EQUIP, EQUIP_TYPE, .......
   WHERE ...............
UNION ALL
  SELECT no, equipment, model, CLLI,
              0, 0, 0, 0,
              case(bandwidth = 'ds1' then ds1equipped else 0 end) ds1equpped,
              case(bandwidth = 'ds3' then ds3equipped else 0 end) ds3equipped,
              case(bandwidth = 'oc12' then oc12equipped else 0 end) oc12equipped,
              case(bandwidth = 'oc3' then oc3equipped else 0 end) oc3equipped,
              0, 0, 0, 0
    FROM  EQUIP, EQUIP_TYPE, .......
   WHERE ..............
UNION ALL
  SELECT no, equipment, model, CLLI,
              0, 0, 0, 0,
              0, 0, 0, 0,
              case(bandwidth = 'ds1' then ds1inuse else 0 end) ds1inuse,
              case(bandwidth = 'ds3' then ds3inuse else 0 end) ds3inuse,
              case(bandwidth = 'oc12' then oc12inuse else 0 end) oc12inuse,
              case(bandwidth = 'oc3' then oc3inuse else 0 end) oc3inuse
     FROM  EQUIP, EQUIP_TYPE, .......
   WHERE ..............

Your result set is returned with the following query:

SELECT no, equipment, model, CLLI,
           sum(ds1wired), sum(ds3wired), sum(oc12wired), sum(oc3wired),
           sum(ds1equipped), sum(ds3equipped), sum(oc12equipped), sum(oc3equipped),
           sum(ds1inuse), sum(ds3inuse), sum(oc12inuse), sum(oc3inuse)
  FROM #temp1
GROUP BY no, equipement, model, CLLI

This will be much faster

Best of luck.
Bill
0
 

Author Comment

by:Sadagopank
ID: 12594864
thanks jan and grant !

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

0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If something goes wrong with Exchange, your IT resources are in trouble.All Exchange server migration processes are not designed to be identical and though migrating email from on-premises Exchange mailbox to Cloud’s Office 365 is relatively simple…
Creating a Cordova application which allow user to save to/load from his Dropbox account the application database.
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Want to learn how to record your desktop screen without having to use an outside camera. Click on this video and learn how to use the cool google extension called "Screencastify"! Step 1: Open a new google tab Step 2: Go to the left hand upper corn…
Suggested Courses

610 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question