83 lines
2.4 KiB
SAS
83 lines
2.4 KiB
SAS
/**
|
|
@file
|
|
@brief migration script to move from v5 to v6.5 of data controller
|
|
|
|
**/
|
|
|
|
%let dclib=YOURDCLIB;
|
|
|
|
libname &dclib "/your/dc/path";
|
|
|
|
/**
|
|
* Change 1
|
|
* New MPE_SUBMIT table
|
|
*/
|
|
proc sql;
|
|
create table &dclib..mpe_xlmap_rules(
|
|
tx_from num not null,
|
|
XLMAP_ID char(32) not null,
|
|
XLMAP_RANGE_ID char(32) not null,
|
|
XLMAP_SHEET char(32) not null,
|
|
XLMAP_START char(1000) not null,
|
|
XLMAP_FINISH char(1000),
|
|
tx_to num not null,
|
|
constraint pk_mpe_xlmap_rules
|
|
primary key(tx_from,XLMAP_ID,XLMAP_RANGE_ID));
|
|
|
|
create table &dclib..MPE_XLMAP_DATA(
|
|
LOAD_REF char(32) not null,
|
|
XLMAP_ID char(32) not null,
|
|
XLMAP_RANGE_ID char(32) not null,
|
|
ROW_NO num not null,
|
|
COL_NO num not null,
|
|
VALUE_TXT char(4000),
|
|
constraint pk_MPE_XLMAP_DATA
|
|
primary key(LOAD_REF, XLMAP_ID, XLMAP_RANGE_ID, ROW_NO, COL_NO));
|
|
|
|
create table &dclib..mpe_xlmap_info(
|
|
tx_from num not null,
|
|
XLMAP_ID char(32) not null,
|
|
XLMAP_DESCRIPTION char(1000) not null,
|
|
XLMAP_TARGETLIBDS char(41) not null,
|
|
tx_to num not null,
|
|
constraint pk_mpe_xlmap_info
|
|
primary key(tx_from,XLMAP_ID));
|
|
|
|
|
|
/* add mpe_tables entries */
|
|
insert into &dclib..mpe_tables
|
|
set tx_from=0
|
|
,tx_to='31DEC5999:23:59:59'dt
|
|
,libref="&dclib"
|
|
,dsn='MPE_XLMAP_INFO'
|
|
,num_of_approvals_required=1
|
|
,loadtype='TXTEMPORAL'
|
|
,var_txfrom='TX_FROM'
|
|
,var_txto='TX_TO'
|
|
,buskey='XLMAP_ID'
|
|
,notes='Docs: https://docs.datacontroller.io/complex-excel-uploads'
|
|
,post_edit_hook='services/hooks/mpe_xlmap_info_postedit'
|
|
;
|
|
insert into &dclib..mpe_tables
|
|
set tx_from=0
|
|
,tx_to='31DEC5999:23:59:59'dt
|
|
,libref="&dclib"
|
|
,dsn='MPE_XLMAP_RULES'
|
|
,num_of_approvals_required=1
|
|
,loadtype='TXTEMPORAL'
|
|
,var_txfrom='TX_FROM'
|
|
,var_txto='TX_TO'
|
|
,buskey='XLMAP_ID XLMAP_RANGE_ID'
|
|
,notes='Docs: https://docs.datacontroller.io/complex-excel-uploads'
|
|
,post_edit_hook='services/hooks/mpe_xlmap_rules_postedit'
|
|
;
|
|
insert into &dclib..mpe_tables
|
|
set tx_from=0
|
|
,tx_to='31DEC5999:23:59:59'dt
|
|
,libref="&dclib"
|
|
,dsn='MPE_XLMAP_DATA'
|
|
,num_of_approvals_required=1
|
|
,loadtype='UPDATE'
|
|
,buskey='LOAD_REF XLMAP_ID XLMAP_RANGE_ID ROW_NO COL_NO'
|
|
,notes='Docs: https://docs.datacontroller.io/complex-excel-uploads'
|
|
; |