Results 1 to 6 of 6
  1. #1
    Join Date
    Feb 2005
    Posts
    4

    Unanswered: Custom "order by"-function

    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

  2. #2
    Join Date
    Jan 2005
    Posts
    191
    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

  3. #3
    Join Date
    Feb 2005
    Posts
    4
    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

  4. #4
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    Have you considered some sort of UDF or SP ?

    Cheers
    Sathyaram
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  5. #5
    Join Date
    Feb 2005
    Posts
    4
    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

  6. #6
    Join Date
    Jan 2005
    Posts
    191
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •