Home » SQL & PL/SQL » SQL & PL/SQL » function - two nested cursor for loop ???
function - two nested cursor for loop ??? [message #201315] Fri, 03 November 2006 08:23 Go to next message
hiip
Messages: 78
Registered: October 2006
Member
hi everyone, really need some help for this one...

the question is ...

fucntion top_client return client.companyname%type;

this function will returns the name of the company/client witht the higest volume of business (in term of campaign cost). to calculate the cost is when the actual cost exxists for a campaign, it is used in calculating the total cost of all the campaigns for a particular client. when it does not exist, the estimated cost is used in calculating the total cost. so, we would not normally know which cost to use for each campaign unless the procedure goes through all the campaigns individually. you are expected to use two nested cursor for loops in this function. one for going through the clients and one for going through the campaigns for each client. while keeping track on the maximum cost so far and the name of the client for that maximum cost...

i will upload my table and data....

can someanyone give me some idea...

this is wat i think of

CURSOR c1 IS SELECT * FROM T1;
CURSOR c2 IS SELECT * FROM T2;
BEGIN
   ...
   FOR each_data IN c1
   LOOP
   /* some business logic */
   END LOOP;
   CLOSE c1;

   /* start another cursor */
   FOR each_row IN c2
   LOOP
      /* compare each row in T1 */
      /* remove non-matched row from T2 */
   END LOOP;
   CLOSE c2;
   ...
END;


  • Attachment: mySql.sql
    (Size: 7.06KB, Downloaded 147 times)
Re: function - two nested cursor for loop ??? [message #201319 is a reply to message #201315] Fri, 03 November 2006 08:41 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
I have to ask - are you going to do ANY of this homework by yourself?
Re: function - two nested cursor for loop ??? [message #201320 is a reply to message #201315] Fri, 03 November 2006 08:42 Go to previous messageGo to next message
hiip
Messages: 78
Registered: October 2006
Member
i am doing it by myself... just could not get wat it means... please dont missunderstand...

cheers... Smile
Re: function - two nested cursor for loop ??? [message #201323 is a reply to message #201320] Fri, 03 November 2006 09:04 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
It's just that in the last 4 days you've posted 59 times asking about this set of problems......
Re: function - two nested cursor for loop ??? [message #201330 is a reply to message #201315] Fri, 03 November 2006 09:26 Go to previous messageGo to next message
hiip
Messages: 78
Registered: October 2006
Member
so please understand me that i am doing myself but just aksing for the problem.... but i myself try to look through books internet document to try my best to understand it and finish it ...
cheers....
Re: function - two nested cursor for loop ??? [message #201331 is a reply to message #201315] Fri, 03 November 2006 09:27 Go to previous messageGo to next message
hiip
Messages: 78
Registered: October 2006
Member
hi others.. can give me some help on this ..??
Re: function - two nested cursor for loop ??? [message #201335 is a reply to message #201315] Fri, 03 November 2006 09:42 Go to previous messageGo to next message
Cthulhu
Messages: 381
Registered: September 2006
Location: UK
Senior Member
Why don't you have a go at solving it yourself, then post your efforts here. But make it something more substantial then the sketchy outline you gave in the original message.
Re: function - two nested cursor for loop ??? [message #201385 is a reply to message #201315] Fri, 03 November 2006 19:02 Go to previous messageGo to next message
hiip
Messages: 78
Registered: October 2006
Member
hi,, i am not asking to solve it.. i am asking for hints and opinion.. you might you words... because i dont really understand this..
Re: function - two nested cursor for loop ??? [message #201388 is a reply to message #201315] Fri, 03 November 2006 20:05 Go to previous messageGo to next message
hiip
Messages: 78
Registered: October 2006
Member
this is my code, is this alright???

FUNCTION top_client RETURN client.companyname%Type
	IS
		CURSOR client_cur IS
			SELECT companyname
			FROM client;
		CURSOR campaign_cur(v_companyname client.companyname%Type) IS
			SELECT estimatedcost, actualcost
			FROM campaign
			WHERE companyname = v_companyname;
		total_cost campaign.actualcost%Type := 0;
	BEGIN
		FOR client_rec IN client_cur LOOP
			FOR campaign_rec IN campaign_cur(client_rec.companyname) LOOP
				IF campaign_rec.actualcost IS NULL THEN
					total_cost := total_cost + campaign_rec.actualcost;
				ELSe
					total_cost := total_cost + campaign_rec.estimatedcost;
				END IF;
				UPDATE campaign
				SET actualcost = total_cost
				WHERE client_rec.companyname = campaign_rec.companyname;
				COMMIT;
			END LOOP;
		END LOOP;
		
		FOR loop_company IN campaign_cur(client.companyname) LOOP
			IF loop_company.actualcost.first < loop_company.actualcost.next(n+1) THEN
				return client;
			END IF;
		END LOOP;
	END top_client;




please correct me if i got something wrong ...


warning message:

Warning: Function created with compilation errors.
Errors for FUNCTION TOP_CLIENT:

LINE/COL ERROR
19/5 PL/SQL: SQL Statement ignored
21/36 PL/SQL: ORA-00904: "CAMPAIGN_REC"."COMPANYNAME": invalid identifi er
21/49 PLS-00302: component 'COMPANYNAME' must be declared
26/3 PL/SQL: Statement ignored
26/43 PLS-00357: Table,View Or Sequence reference 'CLIENT.COMPANYNAME' not allowed in this context

[Updated on: Fri, 03 November 2006 22:28]

Report message to a moderator

Re: function - two nested cursor for loop ??? [message #201393 is a reply to message #201388] Fri, 03 November 2006 22:30 Go to previous messageGo to next message
hiip
Messages: 78
Registered: October 2006
Member
really hope someone can show me something as i try my really best to get this code done ... plus i am still hasnt give up on checking on the code myself..checking.. in progres.s...

and will upload my new code if i change ..

so please give me a hand ..
Re: function - two nested cursor for loop ??? [message #201412 is a reply to message #201393] Sat, 04 November 2006 02:21 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Ok, you want hints and tips?
Tip #1: READ the errormessage. Reread the errormessage. It is very clear.
Quote:
19/5 PL/SQL: SQL Statement ignored
21/36 PL/SQL: ORA-00904: "CAMPAIGN_REC"."COMPANYNAME": invalid identifi er
21/49 PLS-00302: component 'COMPANYNAME' must be declared

What exactly does this say??


Same for the other error.
Quote:
26/3 PL/SQL: Statement ignored
26/43 PLS-00357: Table,View Or Sequence reference 'CLIENT.COMPANYNAME' not allowed in this context

Read and reread until you see what it says.

Coding is all about being secure. My guess is that either you have no idea about what you actually are doing, in which case you should get a beginners book PL/SQL-coding, or you that you know how to do this, but you're being sloppy.
Whichever it is, you learn far more from fixing this yourself (or at least trying to) then asking anything that goes wrong in this forum.

How's that for hints & tips?
Re: function - two nested cursor for loop ??? [message #201456 is a reply to message #201412] Sat, 04 November 2006 07:44 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Quote:
Reported By: hiip On: Sat, 04 November 2006 13:16 In: SQL & PL/SQL SQL & PL/SQL Newbies function - two nested cursor for loop ???

Reason: too stupid to comment
Hiip, it appears that you are trying to drive a Formula 1, but you didn't learn how to drive a bicycle yet.

People keep telling you to read the PL/SQL manual, educate yourself because errors you make are of a truly beginner. It is not a shame to be a beginner. What really is sad, is a fact that you refuse to learn but expect other people to solve your problems which, really, aren't that difficult.

In some topics, it looks like there's bunch of people, either blind, deaf or numb (or any combination of those). Whatever someone says, it seems that you simply don't want to listen to their advices. It is a miracle how Forum members keep to stay calm because sometimes such a behaviour drives you crazy.

The same goes for this topic: Frank gave you tips & tricks you could see in your previous topics, but were too blind to see them, too deaf to hear them. I have that strange feeling that some day you're about to start a topic and nobody will answer to it because there's no point in doing it as you just won't listen. I'm sorry, but that's how I see it.
Re: function - two nested cursor for loop ??? [message #201461 is a reply to message #201456] Sat, 04 November 2006 09:46 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
Reason: too stupid to comment

Woody Allen once said:

Life is divided into the horrible and the miserable.

Is it meant for us? Razz
Re: function - two nested cursor for loop ??? [message #201463 is a reply to message #201461] Sat, 04 November 2006 09:51 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Reported By: hiip On: Sat, 04 November 2006 13:16 In: SQL & PL/SQL SQL & PL/SQL Newbies function - two nested cursor for loop ???

Reason too stupid to comment

LOL
Did it ever occur to you WHO read the reportings?


The irony of it all is that I really WAS trying to help you

[Updated on: Sat, 04 November 2006 10:00]

Report message to a moderator

Re: function - two nested cursor for loop ??? [message #201468 is a reply to message #201463] Sat, 04 November 2006 13:31 Go to previous message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
./fa/1693/0/ Everything's gonna be alright, Frank
Previous Topic: about triggers
Next Topic: Inline Views
Goto Forum:
  


Current Time: Fri Dec 09 09:57:39 CST 2016

Total time taken to generate the page: 0.10858 seconds