Sitemap

Amicuk Programming Answers

Nested group function without group xmlagg

-0001-11-30   Views:6

Advertisement

I am getting nested group function without group by xmlagg when using the xmlagg function inside another xmlagg function. Find the table structure and sample data here, CREATE TABLE "TEST_TABLE"    ("KEY" NUMBER(20,0),     "NAME&q

I am getting nested group function without group by xmlagg when using the xmlagg function inside another xmlagg function. Find the table structure and sample data here,
CREATE TABLE "TEST_TABLE"
   ("KEY" NUMBER(20,0),
    "NAME" VARCHAR2(50 ),
    "DESCRIPTION" VARCHAR2(100 )
   Insert into TEST_TABLE (KEY,NAME,DESCRIPTION) values (1,'sam','desc1');
   Insert into TEST_TABLE (KEY,NAME,DESCRIPTION) values (2,'max','desc2');
   Insert into TEST_TABLE (KEY,NAME,DESCRIPTION) values (3,'peter',null);
   Insert into TEST_TABLE (KEY,NAME,DESCRIPTION) values (4,'andrew',null);
select
        XMLSerialize(document
        xmlelement("root",
         xmlagg(
           xmlelement("emp"          
           , xmlforest(Key as "ID")          
           , xmlforest(name as "ename")
           , xmlelement("Descriptions", 
           xmlagg(
              xmlforest(description as "Desc")
       ) as clob indent
       ) as t   
      from test_table;Then i removed the xmlagg function from the above select query and used xmlelement instead
  select
        XMLSerialize(document
        xmlelement("root",
         xmlagg(
           xmlelement("emp"          
           , xmlforest(Key as "ID")          
           , xmlforest(name as "ename")
           , xmlelement("Descriptions",            
              xmlforest(description as "Desc")
       ) as clob indent
       ) as t   
      from test_table;This is working fine, but xml created with empty elements for Descriptions element for key 3 and 4 which has null values. I need don't need Descriptions element in the xml when it has null value. Please help me to resolve this.

The replay answer
Advertisement
You can do it with a correlated subquery :
SQL> select xmlserialize(document
  2           xmlelement("root",
  3             xmlagg(
  4               xmlelement("emp"
  5               , xmlforest(
  6                   t.key as "ID"
  7                 , t.name as "ename"
  8                 , (
  9                     select xmlagg(
10                              xmlelement("Desc", d.description)
11                              order by d.description -- if necessary
12                            )
13                     from test_desc d
14                     where d.key = t.key
15                   ) as "Descriptions"
16                 )
17               )
18             )
19           ) as clob indent
20         )
21  from test_table t;
XMLSERIALIZE(DOCUMENTXMLELEMEN
<root>
  <emp>
    <ID>1</ID>
    <ename>sam</ename>
    <Descriptions>
      <Desc>desc1_1</Desc>
      <Desc>desc1_2</Desc>
      <Desc>desc1_3</Desc>
    </Descriptions>
  </emp>
  <emp>
    <ID>2</ID>
    <ename>max</ename>
    <Descriptions>
      <Desc>desc2_1</Desc>
      <Desc>desc2_2</Desc>
      <Desc>desc2_3</Desc>
    </Descriptions>
  </emp>
  <emp>
    <ID>3</ID>
    <ename>peter</ename>
  </emp>
  <emp>
    <ID>4</ID>
    <ename>andrew</ename>
  </emp>
</root>
Or an OUTER JOIN + GROUP-BY :
select xmlserialize(document
         xmlelement("root",
           xmlagg(
             xmlelement("emp"          
             , xmlforest(
                 t.key as "ID"
               , t.name as "ename"
               , xmlagg(
                   xmlforest(d.description as "Desc")
                   order by d.description -- if necessary
                 ) as "Descriptions"
         ) as clob indent
from test_table t
     left outer join test_desc d on d.key = t.key
group by t.key
       , t.name
;Edited by: odie_63 on 11 juil. 2012 14:54 - added 2nd option

Go to See the other 9 answers

Nested group function without group xmlagg

Category:DefaultRelease time:-0001-11-30Views:130

I am getting nested group function without group by xmlagg when using the xmlagg function inside another xmlagg function. Find the table structure and sample data here, CREATE TABLE "TEST_TABLE"    ("KEY" NUMBER(20,0),     "NAME&q[More]

Nested group function

Category:DefaultRelease time:-0001-11-30Views:130

Hello all, How can I use a nested group function in a select statement? For example: select sum(count(id), column a, column b from table_name group by??? Thanks in advance!select sum(CNT) column_a, column_b        from  (select count(id), column_a, c[More]

Error in nested group function used with column name.

Category:DefaultRelease time:-0001-11-30Views:130

Hi Team, If i used nested group function with column name its not working. Could you please any one suggest me. How to use it. Regards, Venkat. Please find Spool ........ SQL> select user_name,max(max(CNT)) from (select USER_NAME,count(*) CNT from v$[More]

Using ldap Network Management Functions how to get nested groups that are members of a parent group

Category:DefaultRelease time:-0001-11-30Views:130

Using NetLocalGetMembers() I can get the users withing a group Using NetUserGetGroups() I can get the groups a user is part of. However, I cant find a function that will list the GROUPS that are part of another group. I have a group called APP_ADMIN,[More]

Nested Group Function without Group By Problem

Category:DefaultRelease time:-0001-11-30Views:130

Hey everyone, I have 3 tables as below: TABLES ITEM (Item_no, Item_price, desc) DeliveryItem (delivery_no, item_no, quantity) Delivery (delivery_no, delivery_date) SELECT desc, MAX(SUM(quantity)) FROM DeliveryItem, Item, Delivery WHERE Item.item_no =[More]

Ideal way to report on Nested Groups and their Members - I can get the script to work if there's one group, but not if there is two or more

Category:DefaultRelease time:2015-10-11Views:130

Hi All, I like to keep track of Groups and Users added into our Domain Admins.  Yes, it should always be minimal, but if I can have a daily report just to be certain, I'll sleep better. The following works just fine if there is a single nested group.[More]

Howto create nested groups???

Category:DefaultRelease time:-0001-11-30Views:130

Hello Mac fans, I'm trying to place groups <domain>\schema admins, admins and admin under one group name poweradmins on the mac. I also want to manually map some users to additional groups. I don't have a Mac network, just a shared PowerMac and a wi[More]

XML Publisher nested grouping, sum and order by question

Category:DefaultRelease time:2015-10-11Views:130

Please help in nested group by, sum and order by question My Data look like this: <Header> <Line1>      <Part Number></Part Number>           <Component>                <Component1></Component1>                <[More]

PS CS6: It'd be helpful to have the option to collapse or expand the nested groups and layers.

Category:DefaultRelease time:2015-10-11Views:130

PS CS6: It'd be helpful to have the option to collapse or expand the nested groups and layers all at once.  Autodesk Maya has a similar feature in it's outliner that allows for you to do this.And as Jeffrey Tranberry answered there: • Open/close all[More]

Getting a list of users and permission from a folder including nested groups

Category:DefaultRelease time:2015-10-11Views:130

$project_folder = "\\servername\foldername\foldername1" Get-Acl $project_folder |      ForEach-Object {          $_.Access | Add-Member -MemberType NoteProperty -Name Path -Value $_.Path -PassThru       } |      ft -GroupBy Path -Property Identi[More]

Nested Groups in Component Model

Category:DefaultRelease time:-0001-11-30Views:130

Is it possible to have a nested group in the Component Model? For example, I have a group of metatdata. That group is called Class and consists of the following fields: - Department - Number - Title - Level There can be 1:n of these Classes. These Cl[More]

Hot
I posted a YouTube video so you could hear the noise my MacBook Pro 15 inch laptop is making. Here is the link as well as the copy/pasted description I used on YouTube (too lazy to re-write) : http://www.youtube.com/watch?v=tW8bIOeOv-w Hi guys, Two w [More]
Hi, My clients produces 5 different finshed products always. I want to map the scenario in Repetitive manufacturing. Is it possible in REM to get the actual cost of production of 5 products separately?? fir example can i get cost of finished product [More]
Is it possible to encrypt the source code of the stored procedures so no one can read the content. I want to deliver a compiled code not the source Database 8i and later thanks in advanceWrap Utility<br> <br> Nicolas.Read other 6 answers [More]
Since upgrading Yosemite I have found that Preview.app will not display some PDF documents, the documents open but all the pages are blank. This is a big issue as my work involves reviewing documents that are all in the PDF format. Strangely some PDF [More]
How can I rearrange the pages in a PDF without changing the assigned paged numbers? These correspond to the original document folio numbers.In theory it would be possible to 'record and play back' the page numbers using a folder-level script, but it' [More]
Hello, We are running BOBJ over a BW environment, and we've been asked to help design a Webdynpro interface that reads a user's security credentials in both BW and BOBJ servers and pulls in all reports, Bex or BOBJ, that they have access to. To do th [More]
Any knows how to use versioning feature on OracleDrive I've create library and set Versioning Model to manual , and there is still error no. 58 from Oracle Drive during saving file on that library(directory). If the answer is "not supported" , m [More]
Hello All, We are using SAP SRM 4.0 classi scenario. In shopping cart status the follow on document PR and PO are showing once created. But our requirement is the shopping cart status shold show the follow on document PO once the Final release is app [More]
I need to transfer my stuff from an iMac to a MacBook Pro, both not new. I don't have the original install discs for the iMac. Id like to be able to keep software etc on the iMac to transfer to a family member but I dont want any of my personal infor [More]
Is there a published list of when Infinty 2 will be rolled out? My Infinity 1 speeds were shown to be 5mb upload which is why I changed from ADSL, but I get only 2mb (same as ADSL) I would have been better off with Sky money wise for FTTC at the same [More]