Crosstab sorting question

hpbrantley

<p>I have search the forums but I am not finding a solution to my particular problem.</p><p> </p><p>Here is my issue. My database is Oracle. I have a column that is varchar2 called BLDG. This varchar2 contains mostly numbers but does have alpha characters. I want to sort on this column. Example</p><p> </p><p>1 - text</p><p>2 - text</p><p>3 - text </p><p>122A - text</p><p>122B - text</p><p> </p><p>This is the desired result, however, in the crosstab report it sorts</p><p> </p><p>1 - text</p><p>122A - text</p><p>122B - text</p><p>2 - text</p><p>3 - text</p><p> </p><p>Now, in a plain BIRT report I sort just fine with </p><p> </p><div>ORDER BY LPAD(REGEXP_REPLACE(B.BLDG,'(:digit:)(:alpha:)','1'),10,'0'),  B.BLDG</div><div> </div><div>What this does is strips of the alpha characters and treats it as</div><div>0000000001,0000000002,0000000003,0000000122,0000000122,0000000122 then by</div><div>the 1,2,3,122A,122B, etc.</div><div> </div><div>Like I said, this works fine in a plain report or execute the plain sql but not in the crosstab. Any assistance is greatly appreciated.</div>

kclark

<p>There are a couple of solutions to this.  I solved it by sorting in my query and adding row numbers.  I created a query like this</p><pre class="_prettyXprint _lang-">select @curRow := @curRow + 1 AS row_number, actor_info.first_name, actor_info.last_name, actor_info.film_infofrom actor_infoJOIN (SELECT @curRow := 0) rorder by actor_info.first_name</pre><p>I use row numbers so the data cube keeps my sorting.  My output ends up looking like this:</p><p> </p><p>