
SET SQL DIALECT 3;

SET NAMES WIN1251;

CONNECT 'C:\DeliveryRK7\DB\DELIVERYRK7.FDB' USER 'SYSDBA' PASSWORD 'masterkey';





ALTER TABLE DLV_ORDERS
ADD ORDERPREFIX D_STRING_100
DEFAULT '';

CREATE INDEX DLV_ORDERS_IDX_ORDER_NUM
ON DLV_ORDERS (ORDER_NUM);

commit work;

Update DLV_ORDERS
SET ORDERPREFIX='';

Commit work;

SET TERM ^ ;

create procedure DLV_UPDATEORDERNUM
as
declare variable INTPARAM integer;
begin
  /* Procedure Text */
  intparam=100;
  while (:intparam>0) do begin
    update dlv_orders
    set order_num=substring(ORDER_NUM from :intparam+1 for 15),
    orderprefix=substring(ORDER_NUM from 1 for :intparam)
    WHERE ((substring(ORDER_NUM from :intparam for 1)<'0') or (substring(ORDER_NUM from :intparam for 1)>'9')) and
        (substring(ORDER_NUM from :intparam for 1)<>'');
    intparam=:intparam-1;
  end
end^

SET TERM ; ^

GRANT SELECT,UPDATE ON DLV_ORDERS TO PROCEDURE DLV_UPDATEORDERNUM;

GRANT EXECUTE ON PROCEDURE DLV_UPDATEORDERNUM TO SYSDBA;

commit work;

execute procedure DLV_UPDATEORDERNUM;

Commit work;

drop procedure DLV_UPDATEORDERNUM;

Commit work;

update RDB$RELATION_FIELDS set
RDB$FIELD_SOURCE = 'D_INTEGER'
where (RDB$FIELD_NAME = 'ORDER_NUM') and
(RDB$RELATION_NAME = 'DLV_ORDERS');

commit work;

ALTER TABLE DLV_ORDERS
ADD RECEIVED_SUMM D_DOUBLE
DEFAULT 0 ;

Commit Work;

ALTER DOMAIN D_DOUBLE SET DEFAULT 0;

UPDATE DLV_ORDERS 
SET RECEIVED_SUMM=0;

UPDATE DLV_ORDERS 
SET ORDER_SUMM=0
WHERE ORDER_SUMM IS NULL;


UPDATE DLV_ORDERS 
SET PAYED_SUMM=0
WHERE PAYED_SUMM IS NULL;

UPDATE DLV_ORDERS 
SET DISCOUNT=0
WHERE DISCOUNT IS NULL;

UPDATE DLV_ORDERS 
SET SUMM_WCD=0
WHERE SUMM_WCD IS NULL;

UPDATE DLV_ORDERS 
SET UNPAY=0
WHERE UNPAY IS NULL;

UPDATE DLV_ORDERS 
SET PREPAY=0
WHERE PREPAY IS NULL;

UPDATE DLV_ORDERS 
SET PROMISED=0
WHERE PROMISED IS NULL;



COMMIT WORK;




