Custom "order by"-function

major.tom · #1 (**permalink**) 02-21-05, 13:14

Hi everybody,

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?

I'm using DB2ESE v8.1.7.445 on Windows 2000 SP4.

Thx in advance,
mt

jacampbell · #2 (**permalink**) 02-21-05, 18:25

You need to write your function to generate the sort keys, include the output from the function in the select list, and then include that into the order by list:

select new_sort_seq(base_key), ...from ...
order by 1

James Campbell

major.tom · #3 (**permalink**) 02-22-05, 07:58

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:

--
sort-key-expression
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?

Thx,
mt

sathyaram_s · #4 (**permalink**) 02-23-05, 14:46

Have you considered some sort of UDF or SP ?

Cheers
Sathyaram

major.tom · #5 (**permalink**) 03-03-05, 06:36

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:

Code:

SELECT n.* FROM
(
    SELECT r.*, ROW_NUMBER() OVER() AS rownum
    FROM (SELECT * FROM <someTab> ORDER BY <myFunction>) r
) n
WHERE n.rownum < 30 AND n.rownum >= 20

Is that possible to do with Java on DB2?

Many thanks in advance!

mt

jacampbell · #6 (**permalink**) 03-04-05, 01:19

declare global temporary table work_tab as
(SELECT r.*, <myFunction> as func_result FROM <someTab>) definition only;

insert into session.work_tab r.*, <myFunction> as func_result FROM <someTab>;

select * from session.work_tab
where func_result >= hight_value_so_far
order by func_result
fetch first 10 rows only;

James Campbell

Internet Services

Web Development

Digital Marketing

Consumer Tech