Function returning different value

[rdharmar]

I am passing one parameter to a function and the return datatype is a number.
When I run the sql itself I get the correct value but the function returns a different value.

Thanks

 

[kskid]

You might provide more details on your select as well as identifying what function is not working.

-LW

 

[SantaMufasa]

Yes, RD, kskid correctly requests more details. Please post the contents of the user-defined function and the SELECT statement that is returning unexpected results.

Mufasa
(aka Dave of Sandy, Utah, USA)

http://www.dasages.com

Do you use Oracle and live or work in Utah, USA?
Then click here to join Utah Oracle Users Group on Tek-Tips.

 

[rdharmar]

Here is my query and whether I passs a parameter or not the function returns a different value. I did a workaround by putting the contents of the inner-most query into a global temp table and it works fine. Any way I would still like to know what is wrong with the function. Thank you for your help. I am using Oracle 8i and my function is defined as:
--------------------------------
FUNCTION RPT_INSTITUTEFEE
(
p_srctype_id IN sourcetype.srcidtyp%TYPE
, p_badgesdelivered IN CHAR
, p_payAction IN VARCHAR2
, p_mtgeventtypeid IN NUMBER
, p_col IN NUMBER

) RETURN NUMBER
----------------------------------------------


SELECT sum(totalinvoiced)
, sum(PYMT_ALLOCATION)
FROM
(
SELECT s2.regid
, s2.regnbr
, s2.srcidtype
, s2.srcid
, badgesdelivered
, totalinvoiced
, PYMT_ALLOCATION
, paidyn
FROM
(
SELECT s1.regid
, s1.regnbr
, s1.srcidtype
, ri.srcid
, badgesdelivered
, totalinvoiced
, PYMT_ALLOCATION
, DECODE (SIGN ( ROUND (totalinvoiced, 2)
- ROUND (NVL (pymt_allocation, 0), 2))
,1
,'UNPAID'
,'PAID'
) paidyn
FROM
(
SELECT id.regid
, id.regnbr
, id.srcidtype
, rper.badgesdelivered
, sum(nvl(id.TOTALINVOICED,0)) totalinvoiced
, sum(nvl(id.PYMT_ALLOCATION,0)) PYMT_ALLOCATION
FROM reg_inv_pay_detl id
, RegPerson rper
WHERE id.regid = rper.regid
AND rper.regstsID IS NULL
AND srcidtype = 2 -- events/registration -- pass param events
GROUP BY id.regid
, id.regnbr
, id.srcidtype
, rper.badgesdelivered
) s1
, reginvoice ri
WHERE s1.regid = ri.regid (+)
AND s1.srcidtype = ri.srcidtype
AND s1.badgesdelivered = 'N' -- pass parameter
)s2
, mtgevent ME
, RegMtgEvent RME
WHERE s2.SrcID=RME.MTGEVENTID(+)
AND s2.REGID=RME.REGID(+)
AND RME.MTGEVENTID=ME.MTGEVENTID(+)

 

[lewisp]

This is not the full function definition. Where is the RETURN? Is your cursor explicit or implicit? Which number are you expecting to be returned, sum(totalinvoiced) or sum(PYMT_ALLOCATION)?

You say you pass one parameter to the function, but it is defined with five.

Are you saying the function returns a different number from the SQL when run in this function exactly as you have defined it? If so, there should be no difference because you are not using parameters in your example SQL.

We need to see all of the function code.