X

An Oracle blog about BI Publisher

  • RTF
    June 10, 2008

Hard Core Sorting

Arrived in California today to a beautiful warm and sunny day, great to see the team. With the morning's meetings out of the way it was off to the 600 cafe to get a much missed veggie burrito - its not so much the burrito (although it was as good as ever) it was the fantastic lady who serve's you. Sadly, I have forgotten her name, but she looks so gruff and barks questions at you, what kind of tortilla do you want, beans? black or refried? jalapanos? salsa - hot? Its a fantastic tongue lashing setting you up to savor the flavor of the burrito even more ... forget Qdoba get over to the Oracle cafe in the bottom of 600!



So to todays rather strangely titled entry - hard core sorting. I say hard core purely because of the requirement. Here's the XML:



<REP> 
<G_1>
<NAME_1>Peter</NAME_1>
<SORT_1>6</SORT_1>
</G_1>
<G_1>
<NAME_1>Anna</NAME_1>
<SORT_1>3</SORT_1>
<G_2>
<NAME_2>Paul</NAME_2>
<SORT_2>1</SORT_2>
</G_2>
<G_2>
<NAME_2>Mary</NAME_2>
<SORT_2>11</SORT_2>
</G_2>
</G_1>
<G_1>
<NAME_1>John</NAME_1>
<SORT_1>2</SORT_1>
</G_1>
<G_3>
<NAME_3>Jim</NAME_3>
<SORT_3>4</SORT_3>
</G_3>
<G_3>
<NAME_3>Frank</NAME_3>
<SORT_3>12</SORT_3>
</G_3>
</REP>




Looks innocuous enough until you see the requirement on how it should render ...



Paul 1
John 2
Anna 3
Jim 4
Peter 6
Mary 11
Frank 12

Hmmmm ... well Klaus and Hok Min rose to the challenge and came up with the following:



<?for-each://*[starts-with(name(.),'G_')]?> 

<?sort:./*[starts-with(name(.),'SORT_')];'descending';data-type='number'?>

<?./*[starts-with(name(.),'NAME_')]?>, <?./*[starts-with(name(.),'SORT_')]?>

<?end for-each?>





Why am I sharing this ? Couple of reasons:



1. Its a neat solution to a tough problem - that, I would have pushed back on before answering. Why not get the extraction routine to do all the heavy lifting. I've said many a time, sorting in the template comes at a price. This type of sorting comes at a bigger price, you are sorting on a partial string comparison ... not good! If you can get the query to do the work, get it to do the work!



2. If we ignore the sorting and focus on the 'for-each'. You can see that you are not looping over a given element in the XML but you are looping over a partial string match on the element name. Tres Cool!

  starts-with(ELEMENT, string ) is an XSL function
  name(.) - another XSL function that returns the element name itself, not its contents.



So we are matching on all element names that start with 'G_' - this enables us to treat the nested 'G_2's as if they were at the same level as the G_1 and G_3 elements.
Again, I would question the XML structure and ask why its so badly formed, but I suspect we did not get to see all of it.



I'll try and expand on this theme of not just 'element looping' in more articles, you can create some damn fine reports with an Oracle burrito inside you!

Join the discussion

Comments ( 5 )
  • Chris Brown Friday, June 13, 2008
    Love it! Been trying to work out how on earth to do something similar in one of my templates for the last few days, and sfter reading this it was finished in a couple of hours!
    Cheers Tim!!
    Cj
  • Vadim Nagornyi Wednesday, July 2, 2008
    Great script! A tiny correction is needed: to have the script to produce the desired output, the sorting should be ascending.
  • Tim Wednesday, July 2, 2008
    @Chris - good - its code I know but pretty complex sorting requirement. Glad the tip help
    @Vadim - you're right - apologies, the sort statement should read:
  • Alice Wellard Saturday, July 24, 2010
    I can declare, that in all the time I have spent using the net, examining blogs, I have never read one as helpful and well crafted as this one, I do not by and large comment on web logs however for you personally I felt the need to make an exception, this really is definitely good work and utterly opposite to the drivel, that i spend lots of my time on the internet, reading. Thank you with regard to taking the time and investing your effort to provide your readers with a first class posting. I look forward to reading alot more of your work, yet again cheers. Ann
  • Sixta Evensen Thursday, July 29, 2010
    I truly rate the trouble you've directed at this information. I will be excited for your next write-up.Please that I stumbled onto this an informative and interesting blog. I simply hope you might make another post based on this. It's perfectly worth reading.
Please enter your name.Please provide a valid email address.Please enter a comment.CAPTCHA challenge response provided was incorrect. Please try again.