Sort INDEX BY BINARY_INTEGER
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3

Thread: Sort INDEX BY BINARY_INTEGER

  1. #1
    Join Date
    Jan 2005
    Posts
    4

    Sort INDEX BY BINARY_INTEGER

    I have two columns with type DATE: start_date and end_date, I would like to merge them into one sorted column.

    I can declare: TYPE merge_date_column IS TABLE OF DATE
    INDEX BY BINARY_INTEGER

    And then insert the start_date and end_date columns values into it, but the problem is that I donít know how to sort this type.

    Is there any way to sort this type, or is it any better way to do it?

  2. #2
    Join Date
    Feb 2005
    Posts
    158
    Not quite sure how you want to handle duplicates or if you are using times or just dates.
    If it is just dates and you want a list of unique dates, convert them to J format (days since sometime way back) or YYYYMMDD. Once they are numeric, it is simple to sort.

    EG
    DECLARE
    TYPE tab_num IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
    v_in tab_num;
    v_out tab_num;
    BEGIN
    SELECT TO_CHAR(SYSDATE-ROWNUM,'J')
    BULK COLLECT INTO v_in
    FROM user_objects
    WHERE ROWNUM < 20;
    FOR i IN NVL(v_in.first,0)..NVL(v_in.last,-1) LOOP
    v_out(v_in(i)) := 0;
    END LOOP;
    v_in.DELETE;
    FOR i IN 1..NVL(v_out.COUNT,-1) LOOP
    v_in(i) := v_out.first;
    v_out.DELETE(v_out.first);
    END LOOP;
    FOR i IN 1..NVL(v_in.COUNT,-1) LOOP
    dbms_output.put_line(TO_DATE(v_in(i),'j'));
    END LOOP;
    END;

  3. #3
    Join Date
    Jan 2005
    Posts
    4

    sort INDEX BY BINARY_INTEGER

    Hello,

    Thanks for your help It helps me a loot !!!

    Yael

Posting Permissions

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


Click Here to Expand Forum to Full Width