Hi,
Good Day !
In my current requirement, I have to display the master details relation in separate rows. The Header should show only the master record while detail should show the detail record only.
I written a query using join & union all and the output displayed correctly. However, the code looks very clumsy & bit complicated as i've many levels like master, details, sub-details. So in this case I will have 3 union all to use.
Is there any other way to write a query for this.
Sample query considering Employee & Department Table using Scott Schema is as below:
Note: TestScript available in :
https://livesql.oracle.com/apex/livesql/s/ehjst59eahrc0i4cw7h1le9bi SELECT LEVELS ,
(CASE WHEN LEVELS = 'H' THEN DEPTNO ELSE NULL END) DEPTNO,
(CASE WHEN LEVELS = 'H' THEN DNAME ELSE NULL END) DNAME,
ENAME,
EMP_DEPTNO,
SAL
FROM (
SELECT 'H' LEVELS,DEPTNO,DNAME,NULL ENAME,NULL EMP_DEPTNO,NULL SAL,DEPTNO REF_DEPTNO
FROM DEPT
UNION ALL
SELECT 'D' LEVELS, B.DEPTNO,B.DNAME,A.ENAME,A.DEPTNO,A.SAL,B.DEPTNO REF_DEPTNO
FROM EMP A, DEPT B
WHERE A.DEPTNO = B.DEPTNO
)
ORDER BY REF_DEPTNO,DECODE(LEVELS,'H',1,'D',2);
Output Is:
LEVEL DEPTNO DNAME ENAME EMP_DEPTNO SAL
H 10 ACCOUNTING
D CLARK 10 2450
D MILLER 10 1300
D KING 10 5000
H 20 RESEARCH
D SMITH 20 800
D FORD 20 3000
D ADAMS 20 1100
D SCOTT 20 3000
D JONES 20 2975
H 30 SALES
D ALLEN 30 1600
D JAMES 30 950
D TURNER 30 1500
D BLAKE 30 2850
D MARTIN 30 1250
D WARD 30 1250
H 40 OPERATIONS
Also can it be done using any analytical function?
Thank You,
Regards,
Amit
Why do you need to do this in your query? I'd have thought you could just join the tables:
select * from scott.emp e
join scott.dept d
on d.deptno = e.deptno;
Then define in your app which are the header rows and which the detail. Or if you wanted to get fancy, use XML, JSON or object types to return one header row with the details:
select xmlforest(
d.dname as "deptName",
xmlagg(
xmlelement("employee",
xmlforest(
e.empno as "empno",
e.ename as "empname",
e.job as "job"
)
)
) as "employees"
) as dept_emps
from scott.emp e
right join scott.dept d
on d.deptno = e.deptno
group by d.deptno, d.dname;
<deptName>ACCOUNTING</deptName>
<employees>
<employee>
<works_number>7782</works_number>
<name>CLARK</name>
<job>MANAGER</job>
</employee>
<employee>
<works_number>7934</works_number>
<name>MILLER</name>
<job>CLERK</job>
</employee>
<employee>
<works_number>7839</works_number>
<name>KING</name>
<job>PRESIDENT</job>
</employee>
</employees>
<deptName>RESEARCH</deptName>
<employees>
<employee>
<works_number>7369</works_number>
<name>SMITH</name>
<job>CLERK</job>
</employee>
<employee>
<works_number>7902</works_number>
<name>FORD</name>
<job>ANALYST</job>
</employee>
<employee>
<works_number>7876</works_number>
<name>ADAMS</name>
<job>CLERK</job>
</employee>
<employee>
<works_number>7788</works_number>
<name>SCOTT</name>
<job>ANALYST</job>
</employee>
<employee>
<works_number>7566</works_number>
<name>JONES</name>
<job>MANAGER</job>
</employee>
</employees>
<deptName>SALES</deptName>
<employees>
<employee>
<works_number>7499</works_number>
<name>ALLEN</name>
<job>SALESMAN</job>
</employee>
<employee>
<works_number>7900</works_number>
<name>JAMES</name>
<job>CLERK</job>
</employee>
<employee>
<works_number>7844</works_number>
<name>TURNER</name>
<job>SALESMAN</job>
</employee>
<employee>
<works_number>7698</works_number>
<name>BLAKE</name>
<job>MANAGER</job>
</employee>
<employee>
<works_number>7654</works_number>
<name>MARTIN</name>
<job>SALESMAN</job>
</employee>
<employee>
<works_number>7521</works_number>
<name>WARD</name>
<job>SALESMAN</job>
</employee>
</employees>
<deptName>OPERATIONS</deptName>
<employees>
<employee/>
</employees>
Anyway, assuming you must to this in SQL, here's another way that only accesses each table once:
- Union all the two together as you've done. But make the "levels" column a number. 0 for departments, 1 for employees
- Then use a hierarchical query starting at lev = 0 to link the departments to employees.
select * from (
select 0 lev,deptno,dname,null ename,null sal
from scott.dept
union all
select 1 lev, deptno, null dname,a.ename,a.sal
from scott.emp a
)
start with lev = 0
connect by prior lev = lev - 1
and prior deptno = deptno;
LEV DEPTNO DNAME ENAME SAL
0 10 ACCOUNTING
1 10 CLARK 2,450
1 10 MILLER 1,300
1 10 KING 5,000
0 20 RESEARCH
1 20 SMITH 800
1 20 FORD 3,000
1 20 ADAMS 1,100
1 20 SCOTT 3,000
1 20 JONES 2,975
0 30 SALES
1 30 ALLEN 1,600
1 30 WARD 1,250
1 30 MARTIN 1,250
1 30 BLAKE 2,850
1 30 TURNER 1,500
1 30 JAMES 950
0 40 OPERATIONS
PS - the scott schema already exists in LiveSQL! So there's no need to create it again.