SQL Utility scripts - using system database 7.17.1.8

Download script file: flattenBox.sql

/*------------------------------------------------
 * Flatten a box - sample sql script
 * 
 * This script may be used to flatten a box that 
 * contains one or more documents. 
 * It put all documents toogather into a one document.
 *
 * The script preserves the original oredr of documents and pages.
 * 
 * use the boxid as the only parameter.
 *------------------------------------------------*/

begin
    declare @boxid integer ;
    declare @firstdocobid integer;
    declare curDocs dynamic scroll cursor for 
        select obid from "ADMIN"."Document" where boxid = @boxid order by number;
    declare @pagenumber integer ;
    declare @docobid integer ;

    -- change to original boxid - a value of "ADMIN'."Box" obid column
    set @boxid = -1; 

    select top 1 obid into @firstdocobid from "ADMIN"."Document" where boxid = @boxid order by number ;
    if @firstdocobid is null then
        RAISERROR 23000 'No documents to merge' ;
    end if ;

    select MAX(number)into @pagenumber from "ADMIN"."Page" where obid = @firstdocobid ;
    OPEN curDocs;
    lp: LOOP
        FETCH NEXT curDocs INTO @docobid;
        IF SQLCODE <> 0 THEN LEAVE lp END IF;
        IF @docobid <> @firstdocobid then
            begin 
                UPDATE "ADMIN"."PAGE" SET obid = @firstdocobid, number = number + @pagenumber where obid = @docobid order by number desc ;
                select MAX(number)into @pagenumber from "ADMIN"."Page" where obid = @firstdocobid ;
                -- delete flattened documents
                delete from "ADMIN"."Document" where obid = @docobid ;
                delete from "ADMIN"."Object" where id = @docobid ;
            end ;
        end if ;
    END LOOP;
    CLOSE curDocs;
END;
go
commit work ;

Use of the script is not reversible.

Use the script when nobody modifies the box to be flattened.