|
Replies:
23
-
Pages:
2
[
1
2
| Next
]
-
Last Post:
Jan 29, 2008 11:34 AM
Last Post By: user504214
|
|
|
Posts:
811
Registered:
03/30/01
|
|
|
|
Count Number of Groups
Posted:
Nov 19, 2007 9:23 AM
|
|
|
Here is sample XML: <ROWSET>
<ROW>
<DEPT>A1</DEPT>
<DATA>2112</DATA>
</ROW>
<ROW>
<DEPT>A1</DEPT>
<DATA>7985</DATA>
</ROW>
<ROW>
<DEPT>B1</DEPT>
<DATA>8452</DATA>
</ROW>
<ROW>
<DEPT>C2</DEPT>
<DATA>2159</DATA>
</ROW>
</ROWSET>
Using Table Wizard, I am getting this report: A1 2112
7985
B1 8452
C2 2159
Everything OK, but at the report header I need to count the number of DEPT, i.e. 3. How to get it? Thanks. |
|
|
Posts:
2,268
Registered:
01/10/01
|
|
|
|
Re: Count Number of Groups
Posted:
Nov 20, 2007 6:28 AM
in response to: nagornyi
|
|
|
|
Just use the count function but provide the full path to the DEPT element
count(/ROWSET/ROW/DEPT)
a little long winded but its going to be much faster than
count(.//DEPT)
That will send the parser off searching for all occurrences throughout the XML
Tim
|
|
|
Posts:
811
Registered:
03/30/01
|
|
|
|
Re: Count Number of Groups
Posted:
Nov 20, 2007 7:20 AM
in response to: Tim Dexter
|
|
|
|
Both give count of 4, not 3.
|
|
|
Posts:
2,268
Registered:
01/10/01
|
|
|
|
Re: Count Number of Groups
Posted:
Nov 20, 2007 9:05 AM
in response to: nagornyi
|
|
|
|
Ahhhh ... you tricked me !
|
|
|
Posts:
2,268
Registered:
01/10/01
|
|
|
Posts:
811
Registered:
03/30/01
|
|
|
Posts:
112
Registered:
04/22/06
|
|
|
|
Re: Count Number of Groups
Posted:
Nov 20, 2007 12:02 PM
in response to: Tim Dexter
|
|
|
|
If your data is ordered, then you may simply count all ROWS that have a different DEPT than the previous one, keeping into account the first row of course. This might give you a better performance for large XML files.
<?count(ROW[position()=1 or ./preceding-sibling::ROW1]/DEPT!=DEPT)?>
XPATH 2.0 does provide something similar, but AFAIK XPATH 2.0 does not work in Oracle XMLP yet.
<?count(distinct-values('DEPT')?>
HTH,
Nilanshu.
|
|
|
Posts:
811
Registered:
03/30/01
|
|
|
|
Re: Count Number of Groups
Posted:
Nov 20, 2007 1:43 PM
in response to: Neil M.
|
|
|
|
Thank you, Nilansu, this also works fine.
May I suggest you to augment the Tim's blog entry with this solution?
|
|
|
Posts:
2,268
Registered:
01/10/01
|
|
|
|
Re: Count Number of Groups
Posted:
Nov 20, 2007 2:21 PM
in response to: nagornyi
|
|
|
|
|
|
Posts:
4
Registered:
12/05/07
|
|
|
|
Re: Count Number of Groups
Posted:
Dec 5, 2007 12:10 PM
in response to: Tim Dexter
|
|
|
|
Hi Tim,
These solutions are all great. However i have a unique situation. Using the ROWSET example above, how would you count the distinct number of DEPTs within a ROW. The solutions above provide the distinct count of DEPTs within ROWSET (the entire document) but i am trying to count the distinct number of DEPTs within a ROW. Any help will be deeply appreciated. Thanks
|
|
|
Posts:
2,615
Registered:
07/24/06
|
|
|
|
Re: Count Number of Groups
Posted:
Dec 5, 2007 3:48 PM
in response to: Kazeem
|
|
|
|
Hi Kazeem,
The above example xml, contains,
lot of row within rowset,
and each row contains only one dept within the row.
but you are asking us
"count the distinct number of DEPTs within a ROW"
can you provide your xml here ? with row having more than one dept.
|
|
|
Posts:
4
Registered:
12/05/07
|
|
|
|
Re: Count Number of Groups
Posted:
Dec 5, 2007 5:44 PM
in response to: Vetsrini
|
|
|
|
Thanks Vetsrini, below i am going to use the same but just modified as i think it will be easier to work with...
<ROWSET>
<ROW>
<DEPT>A1</DEPT>
<DEPT>A1</DEPT>
<DEPT>X1</DEPT>
<DEPT>Y1</DEPT>
<DEPT>Z1</DEPT>
<DATA>2112</DATA>
</ROW>
<ROW>
<DEPT>A1</DEPT>
<DATA>7985</DATA>
</ROW>
<ROW>
<DEPT>B1</DEPT>
<DATA>8452</DATA>
</ROW>
<ROW>
<DEPT>C2</DEPT>
<DATA>2159</DATA>
</ROW>
</ROWSET>
...as you can see the first ROW tag has 5 DEPT elements but only 4 distinct ones. The actual application of this is a for a Purchase Order with lines shipping to different locations. I need to be able to find out (for each purchase order) how many distinct locations goods are being shipped to. If you can help with this then i will apply same logic to my actual XML data. Thanks
|
|
|
Posts:
2,615
Registered:
07/24/06
|
|
|
|
Re: Count Number of Groups
Posted:
Dec 5, 2007 7:38 PM
in response to: Kazeem
|
|
|
|
I am guessing , you are looping through the each PO
to identify all the unique locations,
similarly
<?for-each:ROW?>
<?count(xdoxslt:distinct_values(DEPT))?>
<end for-each>
<?count(xdoxslt:distinct_values(/ROWSET/ROW/DEPT))?>
4
1
1
1
6
|
|
|
Posts:
4
Registered:
12/05/07
|
|
|
|
Re: Count Number of Groups
Posted:
Dec 9, 2007 4:45 PM
in response to: Vetsrini
|
|
|
|
Vetsrini,
Thanks for your post. I tried that already and it works except that i have a unique problem. Some Purchase Orders will not have a shipment tag at all hence i am getting a null pointer exception on those. To get the error i am getting, remove <DEPT>B1</DEPT> from the xml you are using to test. This will cause the 3rd ROW tag not to have a department and you will get a null pointer exception. Is there a way to handle this? Thanks
|
|
|
Posts:
2,615
Registered:
07/24/06
|
|
|
|
Re: Count Number of Groups
Posted:
Dec 10, 2007 7:07 AM
in response to: Kazeem
|
|
|
try this
<?count(xdoxslt:distinct_values(DEPT http://.!=’’))?> |
|
|
|
Legend
|
|
Guru : 2500
- 1000000
pts
|
|
Expert : 1000
- 2499
pts
|
|
Pro : 500
- 999
pts
|
|
Journeyman : 200
- 499
pts
|
|
Newbie : 0
- 199
pts
|
|
Oracle ACE Director
|
|
Oracle ACE Member
|
|
Oracle Employee ACE
|
|
Helpful Answer
(5 pts)
|
|
Correct Answer
(10 pts)
|
|
