Sort INDEX BY BINARY_INTEGER

[ylevani]

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?

[gamyers]

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;

[ylevani]

Hello,

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

Yael