Home » Developer & Programmer » JDeveloper, Java & XML » How to write a query
How to write a query [message #360871] Mon, 24 November 2008 03:50 Go to next message
vikram1780
Messages: 222
Registered: January 2007
Location: Bangalore
Senior Member
Hi,

How to write below query without any errors.

SQL> SELECT xmlelement("P",xmlforest(P.process_id AS Ppid),
  2   xmlagg(xmlelement("PI",XMLFOREST( PI.question_id AS PIqid,
  3   PI.process_id AS PIpid,
  4   PI.innertext AS PItext),
  5   xmlagg(Xmlelement("PO",xmlforest( PO.option_id AS POoid,
  6   PO.question_id AS POqid,
  7   PO.process_id AS popid
  8   ))
  9  ORDER BY PO.option_id))
 10     ORDER BY PI.question_id ) )
 11     FROM liveProcess_ec P
 12  INNER JOIN vw_liveProcessItem_Sim_v6 PI
 13       ON P.process_id = PI.process_id
 14  LEFT OUTER JOIN vw_liveProcessOption_Sim_v6 PO
 15       ON PI.question_id = PO.question_id
 16  AND PI.process_id      = PO.process_id
 17    WHERE p.process_id   =450
 18  GROUP BY p.process_id,PI.question_id,PI.process_id,PI.innertext
 19    ORDER BY p.process_id;
SELECT xmlelement("P",xmlforest(P.process_id AS Ppid),
                                *
ERROR at line 1:
ORA-00937: not a single-group group function 


Any Help really appreciated.

Thanks
Re: How to write a query [message #360872 is a reply to message #360871] Mon, 24 November 2008 04:00 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

I ma not pretty sure about xmlelements.

But how come ORDER BY clauses are coming before From clause in a SELECT Clause ?

Smile
Rajuvan
Re: How to write a query [message #360874 is a reply to message #360872] Mon, 24 November 2008 04:06 Go to previous messageGo to next message
vikram1780
Messages: 222
Registered: January 2007
Location: Bangalore
Senior Member

Hi,

Yes we can have order by clause with from clause in above sql statement. But the problem is with group by statement.


Thanks
Re: How to write a query [message #360876 is a reply to message #360871] Mon, 24 November 2008 04:10 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

I Agree that

Quote:
we can have order by clause with from clause in above sql statement


Are you sure that we can have order by clause BEFORE from clause in above sql statement ?

Smile
Rajuvan
Re: How to write a query [message #360879 is a reply to message #360874] Mon, 24 November 2008 04:18 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
You're grouping by a lot of terms thatonly appear inside aggregate functions.
Try restricting your group by clause to terms that only appear outside of aggregate functions - ie p.process_id

Further help is available on presentation of a test case including Create table and insert statements.
Re: How to write a query [message #360885 is a reply to message #360879] Mon, 24 November 2008 04:32 Go to previous messageGo to next message
vikram1780
Messages: 222
Registered: January 2007
Location: Bangalore
Senior Member
Can you just let me know how to restrict to appear outside of aggregate functions.

It will really help a lot for me.

Thanks in advance.
Re: How to write a query [message #360905 is a reply to message #360879] Mon, 24 November 2008 05:46 Go to previous messageGo to next message
vikram1780
Messages: 222
Registered: January 2007
Location: Bangalore
Senior Member
Hi,

Tried in a different way but no luck,

SQL> SELECT xmlagg(xmlelement("P",xmlforest(P.process_id),
  2   xmlagg(xmlelement("PI",XMLFOREST( PI.question_id AS PIqid,
  3   PI.process_id AS PIpid,
  4   PI.innertext AS PItext),
  5   xmlagg(Xmlelement("PO",xmlforest( PO.option_id AS POoid,
  6   PO.question_id AS POqid,
  7   PO.process_id AS popid
  8   ) ) ) ) ) ))
  9     FROM liveProcess_ec P
 10  INNER JOIN vw_liveProcessItem_Sim_v6 PI
 11       ON P.process_id = PI.process_id
 12  LEFT OUTER JOIN vw_liveProcessOption_Sim_v6 PO
 13       ON PI.question_id = PO.question_id
 14  AND PI.process_id      = PO.process_id
 15    WHERE p.process_id   =450
 16  GROUP BY p.process_id,PI.question_id,PI.process_id,PI.innertext
 17    ORDER BY p.process_id;
 xmlagg(Xmlelement("PO",xmlforest( PO.option_id AS POoid,
 *
ERROR at line 5:
ORA-00935: group function is nested too deeply 



Thanks
Re: How to write a query [message #360919 is a reply to message #360885] Mon, 24 November 2008 06:19 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Not without some explanation of what
Quote:
how to restrict to appear outside of aggregate functions
means.

What happens when you try your original query, replacing
GROUP BY p.process_id,PI.question_id,PI.process_id,PI.innertext
with
GROUP BY p.process_id


There's no point posting other queries and telling us that you're trting a different approack when we don't know what you were trying to achieve originally.

Previous Topic: ORA-29535,load java ,class not found
Next Topic: synonym/grant for constraint tables
Goto Forum:
  


Current Time: Fri Aug 01 10:05:11 CDT 2014

Total time taken to generate the page: 0.05392 seconds