insert statement in oracle without using user defined function

Question

I have table employees with columns eno, ename, job, sal, comm and the query like

INSERT a new employee
eno as 7787,
ename as 'abc',
job  as 'salesman'
sal as 2000,
comm as tax amount

this tax is the function like

CREATE OR REPLACE FUNCTION  tax
( p_sal employees.sal%type
)
RETURN NUMBER
IS
v_tax employees.sal%type;
BEGIN
v_tax:= CASE
        WHEN SAL> 4000 THEN SAL * 0.33
        WHEN SAL >2500 THEN SAL *0.25
        WHEN SAL >1500 THEN SAL * 0.20
        ELSE 0
        END;
RETURN v_tax
END tax;

At the INSERT statement I can't use function tax for the column comm. Is there any other method to do this, or how can this be best achieved?

Luke Woodward · Accepted Answer · 2011-03-05 18:15:34Z

2

When you say

I can't use function tax for the column comm

do you mean you're not allowed to use this function, or you can't figure out how to use it?

Assuming the latter, I don't see why you shouldn't be able to use a function in an INSERT statement. You have, however, got the syntax of the INSERT statement completely wrong.

Try

INSERT INTO employee (eno, ename, job, sal, comm)
  VALUES (7787, 'abc', 'salesman', 2000, tax(2000));

I don't know where amount in your INSERT statement comes from, but given that your function takes a parameter called p_sal, I'm guessing it's applied to the value in the column sal.

answered Mar 5, 2011 at 18:15

Luke Woodward

65.3k16 gold badges94 silver badges108 bronze badges

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

Collectives™ on Stack Overflow

insert statement in oracle without using user defined function

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