Home » Developer & Programmer » JDeveloper, Java & XML » Easy XML - Let the Database do the Work (Oracle 10g, Windows)
icon6.gif  Easy XML - Let the Database do the Work [message #316956] Tue, 29 April 2008 00:00 Go to next message
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 #316963 is a reply to message #316956] Tue, 29 April 2008 00:22 Go to previous messageGo to next message
Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Quote:
I tried using
http://www.orafaq.com/cgi-bin/sqlformat/pp/utilities/sqlformatter.tpl

But the output is what is pasted above.

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 Go to previous messageGo to next message
Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
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]

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 Go to previous messageGo to next message
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.

Re: Easy XML - Let the Database do the Work [message #316987 is a reply to message #316976] Tue, 29 April 2008 01:34 Go to previous messageGo to next message
Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
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 Go to previous messageGo to next message
Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA
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 Go to previous messageGo to next message
Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA
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
icon6.gif  Re: Easy XML - Let the Database do the Work [message #318794 is a reply to message #317313] Thu, 08 May 2008 00:34 Go to previous message
priyamalhotra
Messages: 43
Registered: July 2006
Location: none
Member


Hi,

Thanks Kevin for the solution.

Thanks,
Priya.

Previous Topic: unable to create UIX page in jdev 10.1.3.3
Next Topic: How to install JDBC driver for SQLdeveloper
Goto Forum:
  


Current Time: Mon Dec 02 07:24:05 CST 2024