 Easy XML - Let the Database do the Work [message #316956] |
Tue, 29 April 2008 00:00  |
|
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
|
|
|
| Re: Easy XML - Let the Database do the Work [message #316963 is a reply to message #316956 ] |
Tue, 29 April 2008 00:22  |
Michel Cadot
Messages: 15243
Registered: March 2007
Location: Nanterre, France, http://... |
Senior Member |
|
|
But you did use code tags as explained in OraFAQ Forum Guide, "How to format your post?" section.
Regards
Michel
|
|
|
| Re: Easy XML - Let the Database do the Work [message #316964 is a reply to message #316956 ] |
Tue, 29 April 2008 00:27 |
Michel Cadot
Messages: 15243
Registered: March 2007
Location: Nanterre, France, http://... |
Senior Member |
|
|
I don't know what you are trying to do but I recommend you to use basic/standard/good old SQL instead of this not maintanable query.
Regards
Michel
[Updated on: Tue, 29 April 2008 00:27]
|
|
|
| Re: Easy XML - Let the Database do the Work [message #316976 is a reply to message #316964 ] |
Tue, 29 April 2008 01:08 |
|
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.
|
|
|
| Re: Easy XML - Let the Database do the Work [message #316987 is a reply to message #316976 ] |
Tue, 29 April 2008 01:34 |
Michel Cadot
Messages: 15243
Registered: March 2007
Location: Nanterre, France, http://... |
Senior Member |
|
|
| Quote: | This article was posted by Kevin Meade's. Is it possible to mail the queries to him directly.
|
I think he will read this otherwise you can go to his profile and click on PM button.
Regards
Michel
|
|
|
| Re: Easy XML - Let the Database do the Work [message #317311 is a reply to message #316956 ] |
Wed, 30 April 2008 07:56 |
Kevin Meade
Messages: 330
Registered: November 2001 |
Senior Member |
|
|
try re-writing the query to this:
select dept.*
,cast(multiset(
select emp.*
,cast(multiset(
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 project.*
,cast(multiset(
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;
Notice the removal of select * from ( and corresponding ).
Kevin
|
|
|
| Re: Easy XML - Let the Database do the Work [message #317313 is a reply to message #316956 ] |
Wed, 30 April 2008 07:57 |
Kevin Meade
Messages: 330
Registered: November 2001 |
Senior Member |
|
|
try re-writing the query to this:
select dept.*
,cast(multiset(
select emp.*
,cast(multiset(
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 project.*
,cast(multiset(
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;
Notice the removal of select * from ( and corresponding ).
Kevin
|
|
|
| Re: Easy XML - Let the Database do the Work [message #318794 is a reply to message #317313 ] |
Thu, 08 May 2008 00:34 |
|
Hi,
Thanks Kevin for the solution.
Thanks,
Priya.
|
|
|
]
Members
Search
Help
Register
Login
Home
