On Jun 15, 8:14 am, Chris <christopherc...@hotmail.com> wrote:
> Hi All,
>
> I have the following function which calculates and returns a figure.
> What I am trying to do is to put an 'IF' statement in the function to
> say if the returning value is > 28 then update a table in the
> database. I still want the returning figure to be calculated but for
> some reason I am getting the following error:
>
> "report error:
> ORA-14551: cannot perform a DML operation inside a query "
>
> /*This function calculates a value and if the acknowledgment takes
> longer than 28 days the vendor gets 2 penalty points and a
> acknowledgement score of 0*/
>
> FUNCTION car_acknowledged_score
> (p_vendor_id IN NUMBER
> ,p_month IN VARCHAR2
> ,p_year IN VARCHAR2)
> RETURN NUMBER
> IS
>
> v_acknowledged_result NUMBER;
> v_acknowledged_score NUMBER;
>
> BEGIN
>
> SELECT xxmel_apex_vendor_rating_pkg.car_acknowledged_result
> (p_vendor_id, p_month, p_year)
> INTO v_acknowledged_result
> FROM dual;
>
> IF v_acknowledged_result <= 4 THEN v_acknowledged_score := 6;
> ELSIF v_acknowledged_result BETWEEN 4 AND 7 THEN
> v_acknowledged_score := 4;
> ELSIF v_acknowledged_result BETWEEN 7 AND 10 THEN
> v_acknowledged_score := 2;
> ELSIF v_acknowledged_result BETWEEN 10 AND 28 THEN
> v_acknowledged_score := 0;
> ELSIF v_acknowledged_result > 28 THEN v_acknowledged_score := 0;
> --2 points deducted for being greater than 28
> UPDATE xxmel_apex_vrd vrd
> SET vrd.PENATLY_POINTS = (NVL(vrd.PENATLY_POINTS,0) + 2)
> WHERE vrd.DATE_PERIOD_MONTH = p_month
> AND vrd.DATE_PERIOD_YEAR = p_year
> AND vrd.SUPPLIER_ID = p_vendor_id;
> COMMIT;
> END IF;
>
> RETURN ROUND(v_acknowledged_score,1);
>
> END car_acknowledged_score;
>
> Any help would be great, thanks
Create a procedure, not a function.
David Fitzjarrell
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google
Groups "Oracle PL/SQL" group.
To post to this group, send email to Oracle-PLSQL@googlegroups.com
To unsubscribe from this group, send email to
oracle-plsql-unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/Oracle-PLSQL?hl=en
-~----------~----~----~----~------~----~------~--~---
