Home » SQL & PL/SQL » SQL & PL/SQL » how to fetch two times the same explicit cursor:first make a total,second divisions by total (11g r2)
how to fetch two times the same explicit cursor:first make a total,second divisions by total [message #571408] Sun, 25 November 2012 12:01 Go to next message
garciala
Messages: 3
Registered: November 2012
Junior Member
Hi,

I need to open an explicit cursor for making a total: after I have to use the same information of that explicit cursor for dividing a column of the cursor by that total. It is not enough to open close, reopen and reclose because I just obtain one register at the same time and it is the same register two times consecutively.

I don't want to use auxiliary structures cause there are 18000 columns for 10200 rows.

Any ideas?

Thanks beforehand.


FOR i IN 300..300 --18000
LOOP
y:=ymax-ysize*(i+0.5);
FOR j IN 200..500 --10000
LOOP
x:=xmin+xsize*(j+0.5);
pixel:=SDO_GEOMETRY(2001,4326,SDO_POINT_TYPE(x,y,NULL),NULL, NULL);
OPEN HumRel(x,y,pixel);
FETCH HumRel INTO hr,dist;
WHILE HumRel%FOUND LOOP
EXIT WHEN HumRel%NOTFOUND;

--sum the total of dist

FETCH HumRel INTO hr,dist;
END LOOP;
CLOSE HumRel;


--*****divide hr between the total of dist****



END LOOP;
END LOOP;


Re: how to fetch two times the same explicit cursor:first make a total,second divisions by total [message #571409 is a reply to message #571408] Sun, 25 November 2012 12:07 Go to previous messageGo to next message
BlackSwan
Messages: 22845
Registered: January 2009
Senior Member
Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/
Re: how to fetch two times the same explicit cursor:first make a total,second divisions by total [message #571410 is a reply to message #571408] Sun, 25 November 2012 12:24 Go to previous messageGo to next message
Michel Cadot
Messages: 59292
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Welcome to the forum.

Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version, with 4 decimals.

18000 columns? On each row? There is surely a bug on the design!
There is no need to loop on a cursor to get a total.
Have a look at SUM function, maybe in its analytic form.
Or RATIO_TO_REPORT as it seems you want a ratio.
Without more information on your tables and what you want, it is very difficult to say more.
Anyway, it is sure that you don't do it in the correct way.

Regards
Michel
Re: how to fetch two times the same explicit cursor:first make a total,second divisions by total [message #571411 is a reply to message #571410] Sun, 25 November 2012 12:38 Go to previous messageGo to next message
garciala
Messages: 3
Registered: November 2012
Junior Member
Michel,

The explicit cursor is inside of two loops: one of 18000 steps and another of 10200.
Because I have to calculate coordinates: I pass them to the cursor and it tells me the humidity
and distance of meteorological stations that are near that coordinates.

So the cursor only retuns one record each time. That is why I can't make a sum, I think.

Thanks,

Garciala

Re: how to fetch two times the same explicit cursor:first make a total,second divisions by total [message #571413 is a reply to message #571411] Sun, 25 November 2012 13:12 Go to previous messageGo to next message
Michel Cadot
Messages: 59292
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
When I hear "2 loops", I think: a join is missing.
We have no clue of what you do, so we can't help more.

Regards
Michel

[Updated on: Sun, 25 November 2012 13:52]

Report message to a moderator

Re: how to fetch two times the same explicit cursor:first make a total,second divisions by total [message #571414 is a reply to message #571413] Sun, 25 November 2012 13:42 Go to previous messageGo to next message
garciala
Messages: 3
Registered: November 2012
Junior Member
When I say two loops, I mean one for the coordinate y and another one for the coordinate x.
So no joins are missed.

Re: how to fetch two times the same explicit cursor:first make a total,second divisions by total [message #571415 is a reply to message #571414] Sun, 25 November 2012 13:51 Go to previous message
Michel Cadot
Messages: 59292
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
If you say so; anyway we can't help more with what you posted.

Regards
Michel
Previous Topic: can we place insert statement in loop inside anonymous block?
Next Topic: encoding using UTL_I18N.ESCAPE_REFERENCE
Goto Forum:
  


Current Time: Thu Oct 02 01:01:14 CDT 2014

Total time taken to generate the page: 0.13329 seconds