CODEWITHSUNDEEP
oracle-databasefunction
AAA
AAA

Reputation: 2450

Oracle view that passes values from a table to a function?

I am trying to create an Oracle view that will show two columns:

Employee_ID, Department_ID

To get department_id, I need to pass an employee_id into a function which I am doing today with:

select * from TABLE(user.fn_department('dept',employee_id)

This will give me only a list of department_ids for the employee_id I pass into it.

I can get a list of unique employees from the employee table.

How do I combine these two outputs?

Thank you!

Upvotes: 0

Views: 67

Answers (2)

eifla001
eifla001

Reputation: 1157

Try this,

SELECT b.employee_id, a.department_id
  FROM employees b, table(user.fn_department('dept',b.employee_id)) a
 ORDER by b.employee_id

Upvotes: 0

Bob Jarvis - Слава Україні
Bob Jarvis - Слава Україні

Reputation: 50017

You can use something like

CREATE OR REPLACE VIEW USER_DEPT_VIEW AS
  SELECT e.EMPLOYEE_ID,
         USER.FN_DEPARTMENT('dept', e.EMPLOYEE_ID) AS DEPARTMENT_ID
    FROM EMPLOYEE e;

which you could then use as e.g.

SELECT *
  FROM USER_dEPT_VIEW
  WHERE EMPLOYEE_ID = 1234

Best of luck.

Upvotes: 1

Related Questions