Difference between revisions of "OE.DEPTVIEW (view)"

From dbscript Online Help
Jump to: navigation, search
 
Line 21: Line 21:
 
FROM departments d
 
FROM departments d
 
</pre>
 
</pre>
 
=== References ===
 
{| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse"
 
|- style="background:silver"
 
| '''Dependency Type
 
| '''Object Type
 
| '''Referenced Object
 
 
|- valign="top"
 
| Select
 
| Table
 
| [[HR.DEPARTMENTS_(table)|HR.DEPARTMENTS]]
 
 
|- valign="top"
 
| Select
 
| Table
 
| [[HR.EMPLOYEES_(table)|HR.EMPLOYEES]]
 
 
|}
 

Latest revision as of 10:34, 27 August 2011

wikibot[edit]

View OE.DEPTVIEW

Source[edit]

CREATE OR REPLACE VIEW OE.DEPTVIEW AS 
SELECT xmlelement("Department", xmlForest(department_name, location_id),
       (SELECT sys_xmlagg(xmlelement("Employee",xmlForest(last_name,
                                                           job_id,manager_id,
                                                           hire_date,
                                                           salary,
                                                           commission_pct)
                                    ), xmlformat('EmployeeList'))
        FROM employees e WHERE e.department_id = d.department_id
       )) xml
FROM departments d