Home » Developer & Programmer » JDeveloper, Java & XML » Help On Concatenation using XMLAGG function
Help On Concatenation using XMLAGG function [message #581766] Wed, 10 April 2013 07:55 Go to next message
fakru.y
Messages: 34
Registered: May 2007
Member
Hi,
i'm having a scenario like this below. i have metric value for 60 weeks for a product. so basically i'll have 60 records for a single product. i need to combine the metric value for that product and generate a single record. for this i'm using XMLAGG function which is giving me a single record by concatenating the metric value. but the problem is i don't understand on what order the metric values is being concatenated. because when i see the data, concatenation metric value is not continuous weeks metric value. if i use START_WITH and CONNECT_BY clauses that also doesn't change my result. Please help me or provide your valuable suggestions. Below is the XMLAGG function which i'm using in my code.

rtrim(XMLAGG(XMLELEMENT(A,MET||'|')).EXTRACT('//text( )')) XYZ

Thanks in advance

Regards
Fakru.Y
Re: Help On Concatenation using XMLAGG function [message #581769 is a reply to message #581766] Wed, 10 April 2013 08:14 Go to previous messageGo to next message
Michel Cadot
Messages: 59422
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
With any SQL or PL/SQL question, please, Post a working Test case: create table (including all constraints) and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.

Before, Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" or "Preview Quick Reply" button to verify.
Also always post your Oracle version, with 4 decimals.

And FEEDBACK to those that help, apart from from your first topic you NEVER did it, so we can't know if we helped you and so we can't know if it is worth to try to do it now.

Regards
Michel
Re: Help On Concatenation using XMLAGG function [message #581770 is a reply to message #581766] Wed, 10 April 2013 08:33 Go to previous messageGo to next message
flyboy
Messages: 1775
Registered: November 2006
Senior Member
XMLAGG function supports ORDER BY clause, so, if you want to have the output sorted by any criteria, you have to use this clause.
It is described in SQL Language reference book, which is available with other Oracle documentation e.g. online on http://tahiti.oracle.com/

For 11gR2, article about XMLAGG function is placed here: http://docs.oracle.com/cd/E11882_01/server.112/e26088/functions235.htm#i1286333
Re: Help On Concatenation using XMLAGG function [message #581771 is a reply to message #581770] Wed, 10 April 2013 08:56 Go to previous messageGo to next message
fakru.y
Messages: 34
Registered: May 2007
Member
Hi Thanks for your reply. i'll check the links provided. as Michel said i'm posting my queries (create,insert and select) for your reference.

attached file has create script, insert script and select query that i wrote and actual and expected results.

working environment is Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production

Please let me know if still need any more information.

  • Attachment: SCRIPTS.txt
    (Size: 28.50KB, Downloaded 90 times)
Re: Help On Concatenation using XMLAGG function [message #581781 is a reply to message #581771] Wed, 10 April 2013 10:26 Go to previous message
fakru.y
Messages: 34
Registered: May 2007
Member
Thanks Everyone for your valuable suggestion. i included order by clause as "Jiri" said, and it is working. thanks for your support. Michel i'm sorry i didn't know that it will give such a bad impression that if i'm not giving my feedback. i'll make sure i'll provide my feedback in future.
Previous Topic: error
Next Topic: Using java.lang.Math in Oracle
Goto Forum:
  


Current Time: Thu Oct 23 10:59:10 CDT 2014

Total time taken to generate the page: 0.08411 seconds