Calling Function in oracle query

Question

I have a PL/SQL function FN_CALC(emp_sal) which returns a Value. PL/SQL Function is working properly. Using the return value of the function I need to update the my oracle query(ie when function returns 1000 one condition else another condition).

Based on the output of Function I need to update the query. Below is my query.

update EMPLOYEE_MASTER 
set EMP_GRADE = case 
    when EMP_GRADE < (FN_CALC(emp_sal)) 
    then EMP_GRADE ='A'
    end;

Kindly help to solve.That is depending on the output of PL/SQL Function the udpate statement should work.

Regards Prakash

You can call a function from sql query as follows : select function_name(param) from dual; You may execute the update statement I've posted as the answer and accept & up vote it if that works fine, otherwise, post the result/error so that its clear to suggest next steps — JavaHopper
– JavaHopper, Commented Jul 3, 2015 at 13:17

JavaHopper · Accepted Answer · 2015-07-03 13:22:04Z

1

Try this

UPDATE employee_master 
    SET emp_grade = (
    CASE 
        WHEN (emp_grade < (SELECT FN_CALC(emp_sal) FROM DUAL))
        THEN 'A'
        ELSE emp_grade
    END);

edited Jul 3, 2015 at 13:22

answered Jul 3, 2015 at 13:13

JavaHopper

5,6071 gold badge22 silver badges28 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Calling Function in oracle query

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related