 Easy XML - Let the Database do the Work [message #316956] |
Tue, 29 April 2008 00:00  |
priyamalhotra
Messages: 43
Registered: July 2006
Location: none
|
Member |
 
|
|
Hi,
With reference to http://www.orafaq.com/node/1025 Article
<<1>>
When trying to execute
select dept.*
,cast(multiset(select * from (
select emp.*
,cast(multiset(select * from (
select timesheet.*
from timesheet
where timesheet.eno = emp.eno
)
) as c_timesheet
) timesheet_list
from emp
where emp.dno = dept.dno
)
) as c_emp
) emp_list
,cast(multiset(select * from (
select project.*
,cast(multiset(select * from (
select workassignment.*
from workassignment
where workassignment.pno = project.pno
)
) as c_workassignment
) workassignment_list
from project
where project.dno = dept.dno
)
) as c_project
) project_list
from dept;
Get error ora-00904 DEPT.DNO: invalid identifier.
I am unable to resolve the error.
I tried to modify the query as:
select dept.*
,cast(multiset(select * from (
select emp.*
,cast(multiset(select * from (
select timesheet.*
from timesheet, /* ### */ emp /* ### */
where timesheet.eno = emp.eno
)
) as c_timesheet
) timesheet_list
from emp /* ### */,dept /* ### */
where emp.dno = dept.dno
)
) as c_emp
) emp_list
,cast(multiset(select * from (
select project.*
,cast(multiset(select * from (
select workassignment.*
from workassignment/* ### */,project/* ### */
where workassignment.pno = project.pno
)
) as c_workassignment
) workassignment_list
from project/* ### */,dept/* ### */
where project.dno = dept.dno
)
) as c_project
) project_list
from dept;
Then it works fine, but suggest if this is correct as the query is not a correlated subquery after the modification.
<<2>>
Also please suggest to format the sql code,
I tried using
http://www.orafaq.com/cgi-bin/sqlformat/pp/utilities/sqlformatter.tpl
But the output is what is pasted above.
Thanks,
Priya.
[Edit MC: Add code tags, replace color by /* ### */ as color tags are not taken inside code]
[Updated on: Tue, 29 April 2008 00:25] by Moderator Report message to a moderator |
|
|
|
|
|
|
|
| Re: Easy XML - Let the Database do the Work [message #316976 is a reply to message #316964] |
Tue, 29 April 2008 01:08  |
priyamalhotra
Messages: 43
Registered: July 2006
Location: none
|
Member |
|
|
Hi,
I have modified the original query from
select dept.*
,cast(multiset(select * from (
select emp.*
,cast(multiset(select * from (
select timesheet.*
from timesheet
where timesheet.eno = emp.eno
)
) as c_timesheet
) timesheet_list
from emp
where emp.dno = dept.dno
)
) as c_emp
) emp_list
,cast(multiset(select * from (
select project.*
,cast(multiset(select * from (
select workassignment.*
from workassignment
where workassignment.pno = project.pno
)
) as c_workassignment
) workassignment_list
from project
where project.dno = dept.dno
)
) as c_project
) project_list
from dept;
I have modified the original query to
select dept.*
,cast(multiset(select * from (
select emp.*
,cast(multiset(select * from (
select timesheet.*
from timesheet,emp
where timesheet.eno = emp.eno
)
) as c_timesheet
) timesheet_list
from emp,dept
where emp.dno = dept.dno
)
) as c_emp
) emp_list
,cast(multiset(select * from (
select project.*
,cast(multiset(select * from (
select workassignment.*
from workassignment,project
where workassignment.pno = project.pno
)
) as c_workassignment
) workassignment_list
from project,dept
where project.dno = dept.dno
)
) as c_project
) project_list
from dept;
Compare the difference will be easily known to you. The original query is not working but the modified query is working. Just required to know if the changes are correct as the correlated subquery is modified.
This article was posted by Kevin Meade's. Is it possible to mail the queries to him directly.
Thanks,
Priya.
|
|
|
|
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
