Sitemap

Amicuk Programming Answers

Nested group function without group xmlagg

-0001-11-30   Views:5

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
Hi, In this company every evening the automatic batch Job for Billing due list is run but on Nov 23 one Deliver and on Nov 30 another delivery has not gone through i.e. the invoices were not created for these two deliveries. I checked in VF01, the in [More]
Hi, I have a Crystal Report that is based on an Oracle Stored Procedure. This was created using Crystal Reports XI R2 and it is published on a Infoview environment. There are 2 parameteres in  an Oracle Stored Procedure, of which one parameter is Reg [More]
I purchased this monitor about 4 weeks ago. The screen goes black only for a 1-2 seconds and then back to normal. This happens randomly. I am pretty sure I can rule out the GPU since my previous monitor 2412HM was running with it and it didnt go rand [More]
I see the Creative Cloud icon in my applications, but no where can I find a Photoshop icon.Double-click on the Creative Cloud icon and it opens as a panel. Click on the Apps tab. If Photoshop has been installed, you will see a green check mark. Doubl [More]
I created an index.html and index.swf and placed them with the AC_Runactivecontent.js on the server after they worked fine locally. Now they run fine on the latest Opera and IE6, but with Firefox (1.5 and 2) and IE7 (WinXp) the flash keeps looping fo [More]
I have a two-day old G5 DP 2.3ghz running Safari 2.0. There have been three incidents when my mouse's (Logitech MX900 BT mouse) pointer will begin to flutter (blink rapidly) and then Safari will scroll itself down to the bottom of whatever webpage I' [More]
I've been charging my phone all night and the led hasn't been flashing to tell its charging when last night it was charging fine and it won't turn on. And when it diddent turn on it was low battery so what shall I doWafaasif wrote: I've been charging [More]
Hi all, whats the difference (in detail) between sequential and scattered read ? Could you point me to a link ? Thx in advance... StefanSequential Reading means, reading data by using indexes. Scattered reading means, reading data without using index [More]
I recently installed the scom 2012 webconsole, but when I try to access it's fail with an unexpeted error: Please provide the following information to the support engineer if you have to contact Microsoft Help and Support : System.ServiceModel.Commun [More]
how do you unlock the macbook pro that has been remotly locked from the icloud because i forgot my passwordThis question should NOT be answered. Using Find My Mac to lock one's computer is a personal security measure for those whose Macs have been lo [More]