|
|||||||||
|
|||||||||
| |||||||||
|
|||||||||
|
|||||||||
| |||||||||
|
|
|
| |||||||||
 |
|
|
«
Previous Thread
|
Next Thread
»
|
Thread Tools | Search this Thread | Rate Thread | Display Modes |
Dev Shed Forums Sponsor:
|
|
|
|
#1
April 26th, 2005, 06:06 PM
|
|||
|
|||
NVL in a view?
I'm trying to create a view with two different tables.
One table holds the departments (department_id, department_name, manager_id, and location_id). The other table holds employee information. The two fields in common between the tables is the manager_id field. For some unknown reason, the only department that doesn't have a manager in our company is the contracting department. I'd like to create a view that lists all the department_ids, department_names, manager_id, location_id, and full_name. Since the contracting department doesn't have a manager they normally wouldn't be listed, but I would like to list them with a message like "No manager". Here's the code I have. Any ideas on what I need to change? I normally don't work with Oracle too often. Code:
CREATE VIEW deptinfo AS SELECT NVL(TO_CHAR(d.department_id), 'No manager') "department_id", d.department_name, d.manager_id, d.location_id, e.first_name || ' ' || e.last_name AS "Full Name" FROM departments d, employees e WHERE d.manager_id = e.employee_id; Thank you!
|
|
#2
April 26th, 2005, 07:11 PM
|
|||
|
|||
|
Try an outer join
Code:
CREATE VIEW deptinfo AS SELECT NVL(TO_CHAR(d.department_id), 'No manager') "department_id", d.department_name, d.manager_id, d.location_id, e.first_name || ' ' || e.last_name AS "Full Name" FROM departments d, employees e WHERE d.manager_id = e.employee_id (+); Try the SQL as a plain SQL SELECT statement first before you try to create a view with it.
|
|
| Viewing: Dev Shed Forums > Databases > Oracle Development > NVL in a view? |
| Thread Tools | Search this Thread |
| Display Modes | Rate This Thread |
|
|
 |
|
|
Del.icio.us
Digg
Google
Spurl
Blink
Furl
Simpy
Y! MyWeb
Linear Mode
