I'd like to write my own sort function for a special, non-lexicographically sorting of varchars. Is it possible to do this in Java (Stored Procedure)? I know that it is possible to use a custom function for ordering in Oracle but I could'nt find anything for DB2. Can anybody please point me to the right direction?
Thx for your quick reply. After all, I do not know, how to solve my sort problem with this. Perhaps I should tell a bit more:
I have a column which exists of 3 parts. First one is alphanumeric, second one is numeric and third one is alphanumeric again. So I have to split my varchar into three parts and sort them differently. These parts are different in length and they are usually separated by spaces but not always, hence I need to use regular expressions for the split.
I do not know how to achieve this in a single sort-key-expression. I read something about a function I can use there in DB2 manual:
An expression that is not simply a column name or an unsigned integer constant. The query to which ordering is applied must be a subselect to use this form of sort-key. The sort-key-expression cannot include a correlated scalar-fullselect (SQLSTATE 42703) or a function with an external action (SQLSTATE 42845).
I know that DB2 somehow can process Java and I read that I can use a function in sort-key-expression. Is there any way to perform an order by with such a construct?
Sorry, had no time to work on this for the last few days. Well my problem is that I'm more a java developer than a db2 guru. So I programmed the sort function in java but I do not know how to integrate this into DB2.
If I only had to get one resultset and then sort it, I would have done it in my java application. But the problem is that I have to provide paging on the result. So I have to sort at the DB to get the whole set sorted and then cut out my subset for the current page... something like this:
SELECT n.* FROM
SELECT r.*, ROW_NUMBER() OVER() AS rownum
FROM (SELECT * FROM <someTab> ORDER BY <myFunction>) r
WHERE n.rownum < 30 AND n.rownum >= 20