• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1272
  • Last Modified:

PL/SQL Sort number column 50 rows and renumber

Hi,
I have a PL/SQL loop that saves the input of a number for each row that has been saved.

Before I save the numbers to the DB I need to sort them from smallest to highest and then set a new value just using 1,2,3,4,5,etc...
 
Example:
If I have 50 rows and my number column has 5,6,7,8,9,....55
I want to save a new consecutive set replacing 5 with 1, 6 with 2, etc.
If I have them saved as 1,2,3,4,5,6, etc as desired and then I remove 6 rows here and there (left after deletes 3,5,6,7,11,12,13,17,etc) I just want to refresh(save to DB also) to a 1,2,3,4,5,6,7,8,etc

Right now I am handling nulls and just adding 1 to the max number inputed.
l_errmsg := 'Looping through each to get max sort number.';
  FOR i in 1..G_F02.COUNT LOOP
     l_sort_order := nvl(to_number(G_F02(i)),0);
     if l_sort_order > l_sort_order_max then
       l_sort_order_max := l_sort_order;
     end if;
  END LOOP;
 
  FOR i in 1..G_F02.COUNT LOOP
     l_count :=0;
     l_sort_order := to_number(G_F02(i));
     l_resphdr_id := to_number(G_F03(i));
 
     if(l_sort_order is null)then
        l_sort_order_max := l_sort_order_max + 1;
        l_sort_order := l_sort_order_max;
     end if;
 
    Update myTable
     SET SORT_ORDER =  l_sort_order
    where PK_ID = l_resphdr_id;
 
  END LOOP;

Open in new window

0
bcarlis
Asked:
bcarlis
  • 3
1 Solution
 
bcarlisAuthor Commented:
Any PL/SQL guys out there? Jean?
0
 
bcarlisAuthor Commented:
Well, I got it!

Boy didn't get any help on that one..
Declare
    l_count      NUMBER;
    l_myMainId NUMBER;
    l_myvar NUMBER;
    l_myvar_max NUMBER:=0;
    l_errmsg     VARCHAR2(200);
begin
  APEX_COLLECTION.CREATE_OR_TRUNCATE_COLLECTION(p_collection_name => 'MYSORT');
  FOR i in 1..HTMLDB_APPLICATION.G_F02.COUNT LOOP
	APEX_COLLECTION.ADD_MEMBER(
		p_collection_name => 'MYSORT',
		p_c001 => to_number(HTMLDB_APPLICATION.G_F03(i)),
		p_c002 => nvl(to_number(HTMLDB_APPLICATION.G_F02(i)),999999)
		);
 
  END LOOP;
  
  l_count := 0;
  FOR rec in (select c001
		from APEX_collections
	       where collection_name = 'MYCOLL' 
				  order by to_number(c002)) LOOP
 
	l_count := l_count + 1;
	Update myTable
	order_col = l_count
	where pk_id = rec.c001;
	
  END LOOP;
  
  APEX_COLLECTION.DELETE_COLLECTION (
    p_collection_name => 'MYSORT');
 
end;

Open in new window

0
 
bcarlisAuthor Commented:
I had changed the actual code because of problems in showing company code.

So, the answer has a few names different from the original question but this is the exact, except for the names, code that was used.

Good luck, hope it helps!
Bill
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now