run a query using EXECUTE BLOCK to prepair a column for an unique-Index



PHP Snippet 1:

EXECUTE BLOCK 
AS 
    DECLARE VARIABLE ID BI;
    DECLARE VARIABLE REASON XTXT;
    DECLARE VARIABLE LAST_REASON XTXT = '';
BEGIN
      FOR SELECT 
                  ID_STATEMENT, 
                  REASON 
          FROM 
                  STATEMENT
          WHERE
                  ID_STATEMENT > 0
          ORDER BY
                  REASON ASC
          INTO    :ID, 
                  :REASON
      DO 
        BEGIN
              IF (REASON = LAST_REASON) THEN BEGIN
                  UPDATE
                        STATEMENT
                  SET 
                        REASON = :REASON || ' X' || :ID
                  WHERE
                        ID_STATEMENT = :ID;
              END
              LAST_REASON = REASON;
        END
END

PHP Snippet 2:

EXECUTE BLOCK 
AS 
    DECLARE VARIABLE ID BLOB;
    DECLARE VARIABLE REASON XTXT;

BEGIN
FOR SELECT 
            REASON || LIST(ID_STATEMENT, 'X'),
            REASON 
    FROM 
            STATEMENT
    WHERE
            ID_STATEMENT > 0
    GROUP BY
            REASON
    HAVING
            COUNT(*) > 1
    INTO    :ID, 
            :REASON
DO BEGIN
    DELETE FROM
          STATEMENT
    WHERE
          REASON = :REASON;
    INSERT INTO
          STATEMENT
    VALUES
          (:ID, :REASON);
END
END