Sort Fields and ORDER BY INET_ATON

[nurfariha90]

I have to sort a column (sequenceDisplay) containing multilevel listing, up to 4 decimal points such as 1, 1.1, 1.2, 1.3, | 2, 2.1, 2.1.1, 2.1.2, 2.2, 2.3, | 3, 3.1.1, 3.1.2, 3.1.2.1, 3.2, | 4, ........., | 9.1, 9.2, | 10, 11.

Using Sort Fields in Jasper Studio only sort the column in Ascending/Descending order and thus item 10 and 11 fall after 1.3 instead 9.2.

The query used in the Dataset directly called the store procedure. However, I have tested the query which is using ORDER BY INET_ATON(SUBSTRING_INDEX(CONCAT(sequenceDisplay ,'.0.0.0'),'.',4)) and the results are produced correctly and accordingly to the sequence I want. 

Is there any solution to sort the column with multilevel listing accordingly in Jaspersoft Studio?

 

Appreciate any kind of helps and comments from experts.

Thanks a lot!

[anish.rai]

Thank you for posting to the Jaspersoft Community. Our team of experts has read your question and we are working to get you an answer as quickly as we can. If you have a Jaspersoft Professional Subscription plan, please visit https://support.tibco.com/s/ for direct access to our technical support teams offering guaranteed response times.

[jgust]

If you can add another column to your dataset then you should include the result of INET_ATON() or if you have access to row_number() then add that.  Once you add those then it will be just a matter of soring on the new field.

https://www.db-fiddle.com/f/fRG4L9uZvrMc83t3cGqzJ/0

select a.MyValue     , INET_ATON(SUBSTRING_INDEX(CONCAT(a.MyValue ,'.0.0.0'),'.',4)) as INET_ATON_Sort     , row_number() over (order by INET_ATON(SUBSTRING_INDEX(CONCAT(a.MyValue ,'.0.0.0'),'.',4))) as rnfrom (    select '1' as MyValue from dual union all    select '3.1.1'        from dual union all    select '2.1.2'        from dual union all    select '1.1'          from dual union all    select '2.1.1'        from dual union all    select '1.2'          from dual union all    select '3.1.2.1'      from dual union all    select '1.3'          from dual union all    select '2'            from dual union all    select '2.3'          from dual union all    select '3.1.2'        from dual union all    select '2.1'          from dual union all    select '2.2'          from dual union all    select '3'            from dual union all    select '10.9'         from dual union all    select '3.2'          from dual) a[/code]