OraFAQ Forum: SQL & PL/SQL » function problem (merged 8)

Home » SQL & PL/SQL » SQL & PL/SQL » function problem (merged 8)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

function problem (merged 8) [message #384832]

Thu, 05 February 2009 07:02

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

Report message to a moderator

Re: function problem [message #384841 is a reply to message #384832]

Thu, 05 February 2009 07:18

cookiemonster
Messages: 13967
Registered: September 2008
Location: Rainy Manchester

Senior Member

1) Stop multiposting
2) Stop multiposting
3) Stop multiposting

4) Remove the exception handlers

Report message to a moderator

Re: function problem (merged 8) [message #384843 is a reply to message #384832]

Thu, 05 February 2009 07:21

Michel Cadot
Messages: 68761
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

I think posting it 8 times is not enough to get an answer, please add 8 more.

Regards
Michel

Report message to a moderator

Re: function problem [message #384845 is a reply to message #384841]

Thu, 05 February 2009 07:22

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.

Report message to a moderator

Re: function problem [message #384847 is a reply to message #384845]

Thu, 05 February 2009 07:37

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

Report message to a moderator

Re: function problem (merged 8) [message #384850 is a reply to message #384832]

Thu, 05 February 2009 07:46

cookiemonster
Messages: 13967
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?

Report message to a moderator

Re: function problem (merged 8) [message #384852 is a reply to message #384832]

Thu, 05 February 2009 07:52

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

Report message to a moderator

Re: function problem (merged 8) [message #384854 is a reply to message #384832]

Thu, 05 February 2009 07:54

jaccyboy
Messages: 19
Registered: December 2008

Junior Member

im queryng using the project number which is the primary key

Report message to a moderator

Re: function problem (merged 8) [message #384855 is a reply to message #384854]

Thu, 05 February 2009 08:03

cookiemonster
Messages: 13967
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

joicejohn
Messages: 327
Registered: March 2008
Location: India

Senior Member

@jaccyboy,
Try changing the following code:

Quote:

PROFIT_LOSS := v_devEnvironmentCost;

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