Home » Developer & Programmer » JDeveloper, Java & XML » DISTINCT in XMLQuery ??
DISTINCT in XMLQuery ?? [message #278604] Mon, 05 November 2007 02:38 Go to next message
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
Re: DISTINCT in XMLQuery ?? [message #278605 is a reply to message #278604] Mon, 05 November 2007 02:40 Go to previous messageGo to next message
Littlefoot
Messages: 20891
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 ...
Re: DISTINCT in XMLQuery ?? [message #278609 is a reply to message #278604] Mon, 05 November 2007 02:45 Go to previous messageGo to next message
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?
Re: DISTINCT in XMLQuery ?? [message #278620 is a reply to message #278609] Mon, 05 November 2007 03:11 Go to previous messageGo to next message
Littlefoot
Messages: 20891
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.
Re: DISTINCT in XMLQuery ?? [message #278629 is a reply to message #278604] Mon, 05 November 2007 03:35 Go to previous messageGo to next message
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
Re: DISTINCT in XMLQuery ?? [message #278745 is a reply to message #278604] Mon, 05 November 2007 09:29 Go to previous messageGo to next message
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??
Re: DISTINCT in XMLQuery ?? [message #290830 is a reply to message #278745] Tue, 01 January 2008 04:57 Go to previous message
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
Previous Topic: XMLSequence and attribute condition
Next Topic: How to export an empty table to XML file
Goto Forum:
  


Current Time: Sun Dec 04 12:45:43 CST 2016

Total time taken to generate the page: 0.11854 seconds