Home » SQL & PL/SQL » SQL & PL/SQL » function problem (merged 8)
function problem (merged 8) [message #384832] Thu, 05 February 2009 07:02 Go to next message
jaccyboy
Messages: 19
Registered: December 2008
Junior Member
Hi guys.

I've got a function here trying to calculate profit and loss.

The problem is that it's not returning any value.

Can anyone see a problem in this code?


--
----Stored Function
--


CREATE OR REPLACE
Function CALC_PROFIT
(v_project_projectNo IN NUMBER)



RETURN NUMBER IS PROFIT_LOSS NUMBER (8,2);
v_staffNo Staff.staff_staffNo%TYPE;
v_devEnvironmentCost softwareProject.project_devEnvironmentCost%TYPE;
v_adminCost softwareProject.project_adminCost%TYPE;
v_maintenance_cost softwareProject.project_maintenance_cost%TYPE;
v_salePrice softwareProject.project_salePrice%TYPE;
v_amtOfTimeUsed NUMBER(8,2);
v_utp_projectNo usesThirdPartySoftware.utp_projectNo%TYPE;
v_utp_title usesThirdPartySoftware.utp_title%TYPE;
CURSOR c_utp IS
SELECT tps_price
FROM softwareProject, usesThirdPartySoftware, thirdPartySoftware
WHERE usesThirdPartySoftware.utp_projectNo = softwareProject.project_projectNo
AND usesThirdPartySoftware.utp_title = thirdPartySoftware.tps_title;


BEGIN
PROFIT_LOSS :=0;
SELECT project_devEnvironmentCost + v_adminCost + v_maintenance_cost
INTO v_devEnvironmentCost
FROM softwareProject
WHERE project_projectNo = v_project_projectNo;

PROFIT_LOSS := v_devEnvironmentCost;

SELECT sum(ul_amtOfTimeUsed * staff_billingHourlyRate)
INTO v_amtOfTimeUsed
FROM usesLabour, Staff
WHERE usesLabour.ul_projectNo = v_project_projectNo
AND usesLabour.ul_staffNo = Staff.staff_staffNo;

PROFIT_LOSS := PROFIT_LOSS + v_amtOfTimeUsed;

PROFIT_LOSS := PROFIT_LOSS * -1;


SELECT NVL(project_salePrice,0)
INTO v_salePrice
FROM softwareProject
WHERE project_projectNo = v_project_projectNo;

PROFIT_LOSS := PROFIT_LOSS + v_salePrice;



RETURN PROFIT_LOSS;
EXCEPTION
WHEN TOO_MANY_ROWS THEN
return(0);
WHEN NO_DATA_FOUND THEN
return(PROFIT_LOSS);
WHEN others THEN
return(PROFIT_LOSS);
END; --FUNCTION CALC_PROFIT
/


Thanks for any help

Jaccyboy
Re: function problem [message #384841 is a reply to message #384832] Thu, 05 February 2009 07:18 Go to previous messageGo to next message
cookiemonster
Messages: 12403
Registered: September 2008
Location: Rainy Manchester
Senior Member
1) Stop multiposting
2) Stop multiposting
3) Stop multiposting

4) Remove the exception handlers
Re: function problem (merged 8) [message #384843 is a reply to message #384832] Thu, 05 February 2009 07:21 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I think posting it 8 times is not enough to get an answer, please add 8 more.

Regards
Michel
Re: function problem [message #384845 is a reply to message #384841] Thu, 05 February 2009 07:22 Go to previous messageGo to next message
jaccyboy
Messages: 19
Registered: December 2008
Junior Member
I apologise for that.

My connection said it could not connect to the server on the faq site but it was a lie it apperas.

thanks for the help though.
Re: function problem [message #384847 is a reply to message #384845] Thu, 05 February 2009 07:37 Go to previous messageGo to next message
jaccyboy
Messages: 19
Registered: December 2008
Junior Member
still not returning a number.

SQL> print n



N
------



nothing happens.

any suggestion?

sorry for the multiple post again
Re: function problem (merged 8) [message #384850 is a reply to message #384832] Thu, 05 February 2009 07:46 Go to previous messageGo to next message
cookiemonster
Messages: 12403
Registered: September 2008
Location: Rainy Manchester
Senior Member
How about a full cut and paste from sqlplus of you running the function and printing the result.
Also are you sure non of the columns you're querying are null?
Re: function problem (merged 8) [message #384852 is a reply to message #384832] Thu, 05 February 2009 07:52 Go to previous messageGo to next message
jaccyboy
Messages: 19
Registered: December 2008
Junior Member
SQL> --
SQL> ----Stored Function
SQL> --
SQL>
SQL>
SQL> CREATE OR REPLACE
2 Function CALC_PROFIT
3 (v_projectNo IN NUMBER)
4
5
6
7 RETURN NUMBER IS PROFIT_LOSS NUMBER (8,2);
8 v_staffNo Staff.staff_staffNo%TYPE;
9 v_devEnvironmentCost softwareProject.project_devEnvironmentCost%TYPE;
10 v_adminCost softwareProject.project_adminCost%TYPE;
11 v_maintenance_cost softwareProject.project_maintenance_cost%TYPE;
12 v_salePrice softwareProject.project_salePrice%TYPE;
13 v_amtOfTimeUsed NUMBER(8,2);
14 v_utp_projectNo usesThirdPartySoftware.utp_projectNo%TYPE;
15 v_utp_title usesThirdPartySoftware.utp_title%TYPE;
16 CURSOR c_utp IS
17 SELECT tps_price
18 FROM softwareProject, usesThirdPartySoftware, thirdPartySoftware
19 WHERE usesThirdPartySoftware.utp_projectNo = softwareProject.project_projec
20 AND usesThirdPartySoftware.utp_title = thirdPartySoftware.tps_title;
21
22
23 BEGIN
24 PROFIT_LOSS :=0;
25 SELECT project_devEnvironmentCost + v_adminCost + v_maintenance_cost
26 INTO v_devEnvironmentCost
27 FROM softwareProject
28 WHERE project_projectNo = v_projectNo;
29
30 PROFIT_LOSS := v_devEnvironmentCost;
31
32 SELECT sum(ul_amtOfTimeUsed * staff_billingHourlyRate)
33 INTO v_amtOfTimeUsed
34 FROM usesLabour, Staff
35 WHERE usesLabour.ul_projectNo = v_projectNo
36 AND usesLabour.ul_staffNo = Staff.staff_staffNo;
37
38 PROFIT_LOSS := PROFIT_LOSS + v_amtOfTimeUsed;
39
40 PROFIT_LOSS := PROFIT_LOSS * -1;
41
42
43 SELECT NVL(project_salePrice,0)
44 INTO v_salePrice
45 FROM softwareProject
46 WHERE project_projectNo = v_projectNo;
47
48 PROFIT_LOSS := PROFIT_LOSS + v_salePrice;
49
50
51
52 RETURN PROFIT_LOSS;
53 end;
54 /

Function created.

SQL> variable n number
SQL> begin
2 :n :=CALC_PROFIT(1);
3 end;
4 /

PL/SQL procedure successfully completed.

SQL> print n

N
----------



that is from sql*plus

Re: function problem (merged 8) [message #384854 is a reply to message #384832] Thu, 05 February 2009 07:54 Go to previous messageGo to next message
jaccyboy
Messages: 19
Registered: December 2008
Junior Member
im queryng using the project number which is the primary key
Re: function problem (merged 8) [message #384855 is a reply to message #384854] Thu, 05 February 2009 08:03 Go to previous messageGo to next message
cookiemonster
Messages: 12403
Registered: September 2008
Location: Rainy Manchester
Senior Member
jaccyboy wrote on Thu, 05 February 2009 13:54
im queryng using the project number which is the primary key


Not what I asked.
I'm talking about the columns you select.

What is the value of these:
v_adminCost and v_maintenance_cost

[Updated on: Thu, 05 February 2009 08:03]

Report message to a moderator

Re: function problem (merged 8) [message #384858 is a reply to message #384852] Thu, 05 February 2009 08:09 Go to previous messageGo to next message
joicejohn
Messages: 327
Registered: March 2008
Location: India
Senior Member
@jaccyboy,
Try changing the following code:
Quote:

PROFIT_LOSS := v_devEnvironmentCost;


TO
PROFIT_LOSS := NVL(v_devEnvironmentCost, 0);


In Addition, what's the purpose of declaring a cursor (c_utp) which is not being used anywhere in the your code?
[***Added:]
Read up on how NULL values affect your calculation in the Oracle docs

Regards,
Jo

[Updated on: Thu, 05 February 2009 08:18]

Report message to a moderator

Re: function problem (merged 8) [message #384863 is a reply to message #384832] Thu, 05 February 2009 08:33 Go to previous messageGo to next message
jaccyboy
Messages: 19
Registered: December 2008
Junior Member
Thanks guys,

looking at it all now

Re: function problem (merged 8) [message #384875 is a reply to message #384832] Thu, 05 February 2009 09:40 Go to previous message
jaccyboy
Messages: 19
Registered: December 2008
Junior Member
i changed the lot of it.

used a loop with the cursor.

working well now.

thanks for all the help

jaccyboy
Previous Topic: Deleteting child table records
Next Topic: SQL plus report in excel format
Goto Forum:
  


Current Time: Sat Dec 03 11:43:35 CST 2016

Total time taken to generate the page: 0.10423 seconds