OraFAQ Forum: JDeveloper, Java & XML » DISTINCT in XMLQuery ??

Home » Developer & Programmer » JDeveloper, Java & XML » DISTINCT in XMLQuery ??

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

DISTINCT in XMLQuery ?? [message #278604]

Mon, 05 November 2007 02:38

panzertape
Messages: 20
Registered: August 2007

Junior Member

Hi !
I have a problem with duplicate values. So I thought I could get rid of them by using DISTINCT. But I don't know where to place it Sad

Thats my Statement:

select XMLElement
("Table1",
XMLElement
("Table1_ITEM", XMLForest(--elements--) )
,
XMLElement
("Table2",
XMLAgg ( XMLElement
( "Table2_ITEM", XMLForest(--elements--) )
)
,
XMLElement
("Table3",
XMLAgg
(XMLElement
("Table3_ITEM", XMLForest(--elements--) )
)
)
))).getClobVal() AS XML

FROM Table1, Table2, Table3
WHERE Table1.ID = Table2.ID AND Table2.ID = Table3.ID
AND Table1.ID = 9439

GROUP BY --table1 elements--

The Statement works fine but I'm getting duplicate values Sad

So do you have any ideas that could help to solve my problem??
Any help would be appreciated

thanks
panzertape

Report message to a moderator

Re: DISTINCT in XMLQuery ?? [message #278605 is a reply to message #278604]

Mon, 05 November 2007 02:40

Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

I don't know XML, but DISTINCT (in pure SQL statement) must follow the SELECT keyword. So, it would be

SELECT DISTINCT ...

Report message to a moderator

Re: DISTINCT in XMLQuery ?? [message #278609 is a reply to message #278604]

Mon, 05 November 2007 02:45

panzertape
Messages: 20
Registered: August 2007

Junior Member

When I do it this way (just SELECT DISTINCT XMLElement.......), then I'm getting an
ORA-22950: cannot ORDER objects without MAP or ORDER method-error.
Do you know how I can solve this error?

Report message to a moderator

Re: DISTINCT in XMLQuery ?? [message #278620 is a reply to message #278609]

Mon, 05 November 2007 03:11

Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

Oracle says

Quote:

ORA-22950: cannot ORDER objects without MAP or ORDER method

Cause: an object type must have a MAP or ORDER method defined for all comparisons other than equality and inequality comparisons.

Action: Define a MAP or ORDER method for the object type

Unfortunately, I wouldn't know what it really means.

This is a completely blind shot: what happens if you add ORDER BY clause at the end of the statement? Something like

SELECT DISTINCT ...
FROM ...
GROUP BY table elements
ORDER BY table_elements

I believe I should give up as I'm probably talking nonsense here.

Report message to a moderator

Re: DISTINCT in XMLQuery ?? [message #278629 is a reply to message #278604]

Mon, 05 November 2007 03:35

panzertape
Messages: 20
Registered: August 2007

Junior Member

Thanks for trying to help me.
I did it that way (group by ... order by)
But it is still giving me the
ORA-22950: cannot ORDER objects without MAP or ORDER method-error.
any other ideas? Confused

Report message to a moderator

Re: DISTINCT in XMLQuery ?? [message #278745 is a reply to message #278604]

Mon, 05 November 2007 09:29

panzertape
Messages: 20
Registered: August 2007

Junior Member

i tried it with an Inline View....but that didn't work...
I got still the duplicate values Sad

  FROM table1,
  (select distinct * from table2) ab, 
 (select distinct * from table3) a
  WHERE table1.ID = 9439 
  AND table1.ID = ab.ID 
  AND ab.ID = a.ID

What am I doing wrong??

Report message to a moderator

Re: DISTINCT in XMLQuery ?? [message #290830 is a reply to message #278745]

Tue, 01 January 2008 04:57

hrishy
Messages: 25
Registered: August 2005

Junior Member

Hi

How about

 FROM table1,
  (SELECT id,sum(columns) * from table2
   GROUP BY id) ab, 
 (select id,sum(columns) from table3
   GROUP BY id) a
  WHERE table1.ID = 9439 
  AND table1.ID = ab.ID 
  AND table1.ID = a.ID

Report message to a moderator

Previous Topic:	XMLSequence and attribute condition
Next Topic:	How to export an empty table to XML file

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Mon Nov 25 00:01:25 CST 2024