| XML Tag Amounts [message #526016] |
Fri, 07 October 2011 12:02  |
Apps11i
Messages: 14
Registered: August 2008
|
Junior Member |
|
|
Hello Guru's,
Can some one please help me out on the below issue?
i have a requirement that i need to display the totals at the last page. i am carry forwarding the totals to the last page but the totals are incorrect. from my investigation i found that the problem is, it is excluding the amounts where it has comma in it.
Ex. <Amount>10</Amount>
<Amount>20</Amount>
<Amount>1,000</Amount>
the total should be 1030 but it is excluding the 1,000 and showing the total as 30.
thanks,
|
|
|
|
| Re: XML Tag Amounts [message #526039 is a reply to message #526016] |
Fri, 07 October 2011 13:47  |
 |
Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe
|
Senior Member
Account Moderator |
|
|
I don't use XML Publisher so this is probably useless, but nevertheless ... maybe it'll give you a hint.
Can't you use the REPLACE function here, and replace a comma with nothing? Here's an example: I created a table that contains your sample data; here's query that selects amounts:SQL> select extractvalue(col, 'root/Amount[' || level || ']') val
2 from test
3 connect by level <= (select count(*)
4 from test,
5 table(xmlsequence(extract(test.col, '//Amount'))));
VAL
----------------------------------------------------------------------------------
10
20
1,000
SQL>
Now, apply the REPLACE function to the 'VAL':SQL> select replace(extractvalue(col, 'root/Amount[' || level || ']'), ',', '') val
2 from test
3 connect by level <= (select count(*)
4 from test,
5 table(xmlsequence(extract(test.col, '//Amount'))));
VAL
------------------------------------------------------------------------------------
10
20
1000
SQL>
That's much better; now, let's sum those values:SQL> select sum(replace(extractvalue(col, 'root/Amount[' || level || ']'), ',', '')) val
2 from test
3 connect by level <= (select count(*)
4 from test,
5 table(xmlsequence(extract(test.col, '//Amount'))));
VAL
----------
1030
SQL>
Shortly, you'd SUM ( REPLACE ( EXTRACTVALUE ) ).
|
|
|
|
| Re: XML Tag Amounts [message #526045 is a reply to message #526039] |
Fri, 07 October 2011 14:17 |
Apps11i
Messages: 14
Registered: August 2008
|
Junior Member |
|
|
|
Thanks for the reply. As you said we can do it in oracle by using functions but in XML Publisher i tried in doing the sum and replacing. nothing worked for my result.
|
|
|
|
|
|
|
|
|
|
| Re: XML Tag Amounts [message #526537 is a reply to message #526402] |
Tue, 11 October 2011 09:04 |
Apps11i
Messages: 14
Registered: August 2008
|
Junior Member |
|
|
Sure thing. Created a variable at the top of the RTF and by using translate function removed the comma to add the amounts.
<?init-page-total:Total?> (At starting of the RTF).
Use the below to remove the comma and add the amounts.
<?add-page-total:Total;translate(LINE_AMOUNT,',','')?>
Thanks,
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
