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
So from yesterday i started getting random crashes with Firefox and at one point it had froze my screen for a few minutes, now today when i attempted to get into my browser there is about 5 seconds before it instantly closes and sends me a crash repo [More]
I plugged my flip camera in and a program setup thing appeared in the corner, when I clicked on it a lot of different things came up. I tried all of them but they were confusing and I wasn't sure what to do. I tried to click "start FlipShare" bu [More]
Hi, I encountered this error when first deploying my Web App in the Apache Tomcat 5.5.7 Manager and launching my login .jsp: Any ideas? *.tld files? Thsiis a vendors *.war file so I am very unsure of code(jsp's) used. Need more info? Please let me kn [More]
I have decided to use the new IOS 5 beta. From what I've seen, the brand new application i messages works on IOS 5 beta. I updated my third generation iPod touch to the new beta software on iTunes 10.3. Ounce I had finished setting up the new softwar [More]
Hello, I have the following question about the Avalable stock which appears in the MD04 trx. for a material in a Plant. This Stock Available normally includes stock "Unrestricted Use" and one can customize the inclusion of Blocked Stock, Restric [More]
Hi, By mistake the redo log files have been removed. Hence we tried to create the redo log files. After creating it when select * from v$log is done, it is as follows SQL> select * from v$log; GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_C [More]
Hi there, I have a MS-5169 v. 2.1 with Bios AMI v. 3.1 (It used to be v. 2.2). The CPU now is AMD K-2 333 MHz but I have a K-2 400 MHz in my drawer I would like to use !   Is it possible to update the M.B. v. 2.1 to use a K-2 400 MHz  ?   The reason [More]
Hi Experts, Though it is common question, after searching the posts, i could not get proper answer. Hence asking for the help. I have the requirement of printing on both sides. i have 2 pages -> page 1 and page 2 For both the pages, i changed to outp [More]
I just add a control break in the interactive report and I'd like to show each rows for each control break in a new page, or just add some new lines for each control break. Is there a way to do this? Thanks.Looks like a javascript problem. Go to a ja [More]
Hi, I think it is the later case, that is, the current object is being altered by SetValue. Maybe you can trace the action by creating a DomainClass from TextNullable and run it in debug. Regards, Peter Sham. -----Original Message----- From: Dimitar [More]