|
The Ultimate Table Compare
MainScript
COM*********************************************************
COM batch name: _Main_Table_Compare_2_Tables
COM Written by: Porter Broyles
COM Date: 2006
COM This code is intended to compare two tables to ensure that they have the same format.
COM It was originally written to ensure that the data contained on one table is the same as the data
COM provided on a new table. This might occur when performing a system upgrade or comparing
COM two reports provided by a client/department.
COM This code will allow the user to select the tables and field names as it is fully automated.
COM********************************************************
SET SAFETY OFF
DELETE ALL OK
delete old_field_names ok
delete format old_field_names ok
delete new_field_names ok
delete format new_field_names ok
delete temp_table1 ok
delete format temp_table1 ok
delete _Old_Field_Names2_ ok
delete format _Old_Field_Names2_ ok
delete _New_Field_Names2_ ok
delete format _New_Field_Names2_ ok
delete Old_Field_Names_wk1 ok
delete format Old_Field_Names_wk1 ok
DELETE Different_Values_Results1 OK
DELETE FORMAT Different_Values_Results1 OK
COMMENT Script is currently set to default the field lengths at 30 characters, if you need more simply change the following variable:
V_field_length = 30
SET SAFETY OFF
DO batch Step1_Define_Table_Similarities
OPEN %v_table_name%
INDEX on %New_Unique% to "New_Unique_1"
Set INDEX TO "New_Unique_1"
OPEN %v_table_name_orig%
DEFINE RELATION %Old_Unique% With %v_table_name% INDEX New_Unique_1
OPEN New_Field_Names
V_count = 1
count
V_length = count1
Do Step2_New_Field_Variables WHILE V_count <= V_length
OPEN Different_values_Results1
SET SAFETY ON
Subscript: Step1_Define_Table_Similarities
com* batch name: Step1_Define_Table_Similarities
DIALOG (DIALOG TITLE "ACL - Data Definition Table" WIDTH 476 HEIGHT 181 ) (BUTTONSET TITLE "&OK;&Cancel" AT 372 108 DEFAULT 1 ) (ITEM TITLE "f" TO "v_table_name" AT 48 48 WIDTH 272 ) (TEXT TITLE "Select New Table:" AT 48 28 ) (TEXT TITLE "Select Original Table" AT 48 88 ) (ITEM TITLE "f" TO "v_Table_name_orig" AT 48 120 WIDTH 274 )
com* Capture New Field Names
OPEN %v_table_name%
DELETE New_recno OK
DELETE Old_recno OK
DEFINE FIELD New_recno COMPUTED RECNO()
DIALOG (DIALOG TITLE "User Dialog" WIDTH 496 HEIGHT 236 ) (BUTTONSET TITLE "&OK;&Cancel" AT 370 12 DEFAULT 1 ) (TEXT TITLE "Select the Primary Key/Unique identifier for the New Table:" AT 24 64 ) (ITEM TITLE "CNDL" TO "New_Unique" AT 48 96 WIDTH 396 )
SET LOG TO Temp_Log
DISPLAY
SET LOG
IMPORT PRINT TO "New_Field_Names2" "New_Field_Names2.FIL" FROM "Temp_Log.LOG" 0 RECORD "Detail" 0 1 0 TEST 0 2 AT 1,1,1 0 "E" TEST 1 0 AT 1,1,0 0 "Log" TEST 1 0 AT 1,1,0 0 "Name" TEST 1 0 AT 1,1,0 7 "Switching" FIELD "full_record" C AT 1,1 SIZE 100,1 DEC 0 WID 88 PIC "" AS "" OPEN New_Field_Names
DEFINE FIELD field_name COMPUTED SUBSTR(SPLIT(full_record, " ", 1), 1, 33)
SORT ON field_name D TO "New_Field_Names"
DELETE FORMAT "New_Field_Names2" OK
DELETE "New_Field_Names2.FIL" OK
DELETE Temp_Log.LOG OK
com* Capture Old Field Names
OPEN %v_table_name_orig%
DELETE New_recno OK
DELETE Old_recno OK
DEFINE FIELD Old_recno COMPUTED RECNO()
DIALOG (DIALOG TITLE "User Dialog" WIDTH 496 HEIGHT 236 ) (BUTTONSET TITLE "&OK;&Cancel" AT 370 12 DEFAULT 1 ) (TEXT TITLE "Select the Primary Key/Unique identifier for the Old Table:" AT 24 64 ) (ITEM TITLE "CNDL" TO "Old_Unique" AT 48 96 WIDTH 396 )
SET LOG TO Temp_Log
DISPLAY
SET LOG
IMPORT PRINT TO "Old_Field_Names2" "Old_Field_Names2.FIL" FROM "Temp_Log.LOG" 0 RECORD "Detail" 0 1 0 TEST 0 2 AT 1,1,1 0 "E" TEST 1 0 AT 1,1,0 0 "Log" TEST 1 0 AT 1,1,0 0 "Name" TEST 1 0 AT 1,1,0 7 "Switching" FIELD "full_record" C AT 1,1 SIZE 100,1 DEC 0 WID 88 PIC "" AS "" OPEN Old_Field_Names
DEFINE FIELD field_name COMPUTED SUBSTR(SPLIT(full_record, " ", 1), 1, 33)
SORT ON field_name D TO "Old_Field_Names"
DELETE FORMAT "Old_Field_Names2" OK
DELETE "Old_Field_Names2.FIL" OK
DELETE Temp_Log.LOG OK
CLASSIFY on field_name to Temp_Table OPEN
SORT on field_name D to Temp_Table1 OPEN
del "Temp_Table.fil" OK
del format Temp_Table OK
OPEN Temp_table1
Delete V_list ok
v_list=blanks(1000)
GROUP
v_list=alltrim(field_name)+ ";" +v_list
end
Subscript: Step2_New_field_Variables
com* batch name: Step2_New_Field_Variables
OPEN New_Field_Names
ASSIGN NFV%v_count% = ALLTRIM(RECOFFSET(field_name, v_count-1))
OPEN Old_Field_Names
V_merged = nfv%v_count%
DIALOG (DIALOG TITLE "Data contained on the new table in the field " WIDTH 657 HEIGHT 674 ) (BUTTONSET TITLE "&OK;&Cancel" AT 504 24 DEFAULT 1 ) (TEXT TITLE "Select and Match from the List Below the old field name in the list with the New Table data field name called: %v_merged%" AT 48 28 WIDTH 368 HEIGHT 53 ) (DROPDOWN TITLE "%v_list%" TO "OFV_v_count" AT 48 96 WIDTH 408 HEIGHT 522 )
ofv%v_count% = "%OFV_v_count%"
OPEN %V_Table_Name_orig%
DELETE Different_Values_%V_count% OK
DELETE FORMAT Different_Values_%V_count% OK
EXTRACT FIELD %v_count% as "test" %old_unique% as "Old_Key_1" %V_Table_Name%.%new_unique% as "New Key 1" %V_Table_Name%.%V_merged% %OFV_V_COUNT% as "Old_Data_1" "%V_merged%" as "New Name_1" "%OFV_V_COUNT%" as "Old Name_1" IF %V_Table_Name%.%V_merged% <> %OFV_V_COUNT% TO "Different Values %V_count%.fil"
OPEN Different_Values_%V_count%
DELETE New_Data OK
DELETE Old_DAta OK
DELETE New_Name OK
DELETE OLD_NAME OK
DELETE Old_Key OK
DELETE New_Key OK
IF FTYPE("%V_merged%") = "C" DEFINE FIELD NEW_Data COMPUTED UPPER(ALLTRIM(SUBSTRING(%V_merged%,1,%V_field_length%)))
IF FTYPE("%V_merged%") = "N" DEFINE FIELD NEW_Data COMPUTED UPPER(ALLTRIM(STRING(%V_merged%,%V_field_length%)))
IF FTYPE("%V_merged%") = "D" DEFINE FIELD NEW_Data COMPUTED UPPER(ALLTRIM(SUBSTRING(DATE(%V_merged%),1,%V_field_length%)))
IF FTYPE("OLD_DATA_1") = "C" DEFINE FIELD Old_Data COMPUTED UPPER(ALLTRIM(SUBSTRING(Old_Data_1,1,%V_field_length%)))
IF FTYPE("OLD_DATA_1") = "N" DEFINE FIELD Old_Data COMPUTED UPPER(ALLTRIM(STRING(Old_Data_1,%V_field_length%)))
IF FTYPE("OLD_DATA_1") = "D" DEFINE FIELD Old_Data COMPUTED UPPER(ALLTRIM(SUBSTRING(DATE(Old_Data_1),1,%V_field_length%)))
DEFINE FIELD NEW_NAME COMPUTED ALLTRIM(SUBSTRING(NEW_NAME_1,1,15))
DEFINE FIELD OLD_NAME COMPUTED ALLTRIM(SUBSTRING(OLD_NAME_1,1,15))
DEFINE FIELD New_Key COMPUTED ALLTRIM(SUBSTRING(New_key_1,1,15))
DEFINE FIELD Old_Key COMPUTED ALLTRIM(SUBSTRING(OLD_key_1,1,15))
EXTRACT FIELD New_Data New_Name New_Key Old_Data Old_Name Old_Key to "Different Values Results1.fil" APPEND
DELETE Different_Values_%V_count% OK
DELETE FORMAT Different_Values_%V_count% OK
V_count = V_count+1
display variable
|