OraFAQ Forum: JDeveloper, Java & XML

Home » Developer & Programmer » JDeveloper, Java & XML » How to write a query

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

How to write a query [message #360871]

Mon, 24 November 2008 03:50

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

Report message to a moderator

Re: How to write a query [message #360872 is a reply to message #360871]

Mon, 24 November 2008 04:00

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

Report message to a moderator

Re: How to write a query [message #360874 is a reply to message #360872]

Mon, 24 November 2008 04:06

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

Report message to a moderator

Re: How to write a query [message #360876 is a reply to message #360871]

Mon, 24 November 2008 04:10

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

Report message to a moderator

Re: How to write a query [message #360879 is a reply to message #360874]

Mon, 24 November 2008 04:18

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.

Report message to a moderator

Re: How to write a query [message #360885 is a reply to message #360879]

Mon, 24 November 2008 04:32

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.

Report message to a moderator

Re: How to write a query [message #360905 is a reply to message #360879]

Mon, 24 November 2008 05:46

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

Report message to a moderator

Re: How to write a query [message #360919 is a reply to message #360885]

Mon, 24 November 2008 06:19

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.

Report message to a moderator

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

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Wed Nov 27 18:12:08 CST 2024