Index: openacs-4/packages/dotlrn/sql/oracle/communities-tree-create.sql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/dotlrn/sql/oracle/communities-tree-create.sql,v
diff -u -r1.3 -r1.4
--- openacs-4/packages/dotlrn/sql/oracle/communities-tree-create.sql	24 Jun 2002 21:10:48 -0000	1.3
+++ openacs-4/packages/dotlrn/sql/oracle/communities-tree-create.sql	28 Jun 2002 00:21:21 -0000	1.4
@@ -18,16 +18,16 @@
 before insert on dotlrn_community_types
 for each row
 declare
-    v_parent_sortkey dotlrn_community_types.tree_sortkey%TYPE;
-    v_max_child_sortkey dotlrn_community_types.max_child_sortkey%TYPE;
+    v_parent_sortkey                dotlrn_community_types.tree_sortkey%TYPE;
+    v_max_child_sortkey             dotlrn_community_types.max_child_sortkey%TYPE;
 begin
 
     if :new.supertype is null then
         -- if this is the root community_type we leave it's sortkey as null
         return;
     else
         -- else get the max_child_sortkey of the parent community_type
-        select tree_sortkey, max_child_sortkey
+        select nvl(tree_sortkey, ''), max_child_sortkey
         into v_parent_sortkey, v_max_child_sortkey
         from dotlrn_community_types
         where community_type = :new.supertype
@@ -51,41 +51,40 @@
 before insert on dotlrn_communities_all
 for each row
 declare
-    v_parent_sortkey dotlrn_communities_all.tree_sortkey%TYPE;
-    v_max_child_sortkey dotlrn_communities_all.max_child_sortkey%TYPE;
+    v_parent_sortkey                dotlrn_communities_all.tree_sortkey%TYPE;
+    v_max_child_sortkey             dotlrn_communities_all.max_child_sortkey%TYPE;
 begin
 
     if :new.parent_community_id is null then
-        -- if this is the root community we get the sortkey from it's parent
-        -- community_type
-        select tree_sortkey, max_child_sortkey
+
+        select nvl(tree_sortkey, ''), max_child_sortkey
         into v_parent_sortkey, v_max_child_sortkey
         from dotlrn_community_types
         where community_type = :new.community_type
         for update of max_child_sortkey;
+
+        v_max_child_sortkey := tree.increment_key(v_max_child_sortkey);
+
+        update dotlrn_community_types
+        set max_child_sortkey = v_max_child_sortkey
+        where community_type = :new.community_type;
+
     else
-        -- else get the max_child_sortkey of the parent community_type
-        select tree_sortkey, max_child_sortkey
+
+        select nvl(tree_sortkey, ''), max_child_sortkey
         into v_parent_sortkey, v_max_child_sortkey
         from dotlrn_communities_all
         where community_id = :new.parent_community_id
         for update of max_child_sortkey;
-    end if;
 
-    -- increment the sort_key
-    v_max_child_sortkey := tree.increment_key(v_max_child_sortkey);
+        v_max_child_sortkey := tree.increment_key(v_max_child_sortkey);
 
-    if :new.parent_community_id is null then
-        update dotlrn_community_types
-        set max_child_sortkey = v_max_child_sortkey
-        where community_type = :new.community_type;
-    else
         update dotlrn_communities_all
         set max_child_sortkey = v_max_child_sortkey
         where community_id = :new.parent_community_id;
+
     end if;
 
-    -- generate the sortkey for the current row
     :new.tree_sortkey := v_parent_sortkey || v_max_child_sortkey;
 
 end dotlrn_communities_in_tr;