-
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?
-
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;
-
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|