|
|
This script was written primarily as a challenge to see if it was possible. It was written as Porter's 1000th post on the official ACL User Forum. While the code generally works, it may still have some bugs. Some people have notified me that they get error messages, but did not provide enough information to troubleshoot the issue.
The code is designed to allow a user to append any number of tables together assuming that they have the same format. The code will open each table and compare the field length for each table and preserve the longest value. It will also identify the field type of the first record. Whatever the field type is of the first record is considered to be the correct value. After determining the field type and the maximum length, ACL will open each file and extract the fields into one large table.
While the code works, it is on the slow side and in all honesty, I do not use it. I present it here more as an example of what could be and for some of the advanced concepts utilized in the code.
COM**************************************************************** COM Written by Porter Broyles COM Date April 2007 COM Posted on the ACL User Forum and then again on the TexasACL.COM website. COM****************************************************************
SET SAFETY OFF
DELETE ALL OK
COM *****************************************************
COM Select the table that serves as the parent table.
COM Also selecting the date format and the name for the extract.
COM *****************************************************
DELETE FORMAT TEMP_1 OK
DELETE TEMP_1.fil OK
DELETE FORMAT TEMP_child OK
DELETE TEMP_child.fil OK
DELETE FORMAT TEMP_parent1 OK
DELETE TEMP_parent1.fil OK
DELETE FORMAT TEMP_parent OK
DELETE TEMP_parent.fil OK
DELETE FORMAT temp_parent OK
DELETE temp_parent.FIL OK
DELETE l_parent.LOG OK
DELETE l_parent.lix OK
DELETE FORMAT temp_child OK
DELETE temp_child.FIL OK
DELETE l_child.LOG OK
DELETE l_child.lix OK
DIALOG (DIALOG TITLE "User Dialog" WIDTH 566 HEIGHT 506 ) (BUTTONSET TITLE "&OK;&Cancel" AT 444 24 DEFAULT 1 ) (TEXT TITLE "Select Parent Table to be Joined" AT 24 172 ) (ITEM TITLE "f" TO "t_parent" AT 72 204 WIDTH 369 HEIGHT 249 DEFAULT "table_1" ) (TEXT TITLE "Select the parent table the guides the Join." AT 24 28 ) (TEXT TITLE "This table will dictate the fields that are included." AT 24 64 ) (TEXT TITLE "It will also dictate the field type of the output." AT 24 100 ) (TEXT TITLE "It will not dictate the field size" AT 24 136 ) (TEXT TITLE "Write the name of the final product" AT 24 472 ) (TEXT TITLE "Write the format of the Date Data EG MM-DD-YYYY" AT 24 436 ) (EDIT TO "v_date" AT 300 432 DEFAULT "MMDDYYYY" ) (EDIT TO "v_product" AT 300 468 DEFAULT "New Report" )
DELETE FORMAT "%v_product%" OK
DELETE "%v_product%.fil" OK
COM ********************************************************
COM Select the various tables that will serve as children tables.
COM This will allow the user to select as many tables as desired.
COM ********************************************************
v_flag = t
v_child_list = blank(1000)
DO D01_child_list WHILE v_flag
COM *******************************************************
COM Assigning the variables:
COM v_script_Loop counts the number of children tables.
COM v_list_ftype the list of ftypes
COM v_script_cnt controls the number of times the script/group is run.
COM v_list_length is the list of field lengths.
COM *******************************************************
v_script_loop = OCCURS("%v_child_list%","|")
v_list_ftype = Blank(100)
v_script_cnt = 1
v_list_parent = blank(1000)
DO D02_Parent_Details
COM **********************************************
COM Resetting the V_script_cnt variable
COM Beginnging subscript to determine length of each field.
COM **********************************************
v_script_cnt = 1
DO D03_Determine_field_length WHILE v_script_cnt <= v_script_loop
COM ****************************************************
COM V_Loop_ctrl determines the number of fields in the file to run subscripts that many times.
COM v_cnt_ctrl keeps track of the count
COM v_extract will be the extract that is generated on all the tables.
COM ****************************************************
v_loop_ctrl = OCCURS("%v_list%","|")
v_cnt_ctrl = 1
v_extract = blank(2000)
DO D04_Define_Parent WHILE v_cnt_ctrl <= v_loop_ctrl
EXTRACt %v_extract% TO "%v_product%.fil"
EXTRACt %v_extract% TO "pARENTS.fil"
COM ********************************************************
COM resetting v_script_cnt to run on Children tables.
COM *********************************************************
v_script_cnt = 1
DO D05_Define_Child_a WHILE v_script_cnt <= v_script_loop
COM *******************************************************
COM Best practice is to have a clean up script at the end of you scripts
COM *******************************************************
DO D99_Clean_UP
**********************************D01_Child_list
COM *********************************************************
COM This script is run repeatedly until the box is checked.
COM Each time it is run it adds to the v_child_list variable.
COM Note that the most recent addition goes at the start. In other words
COM if you load the tables 1-2-3 it will appear in the variable
COM as 3-2-1.
COM **********************************************************
DIALOG (DIALOG TITLE "User Dialog" WIDTH 532 HEIGHT 434 ) (BUTTONSET TITLE "&OK;&Cancel" AT 36 192 DEFAULT 1 ) (TEXT TITLE "Select Child Table" AT 24 40 ) (ITEM TITLE "f" TO "t_child" AT 36 72 WIDTH 366 HEIGHT 326 DEFAULT "table_2" ) (CHECKBOX TITLE "Check box if you have another table to load" TO "v_flag" AT 36 156 )
v_child_list = "%t_child% |"+ALL(v_child_list)
****************************D02_Parent_Details
COM*************************************
COM Since I'm using SET LOG to create new tables, I'm deleting the logs just in case the logs
COM already existed. It is generally a best practice to DELETE variables or tables/fields/logs
COM before creating them in your script. This way you avoid potential problems with inadvertent
COM append or additions that you weren't expecting.
COM ************************************
DELETE l_parent.log OK
DELETE l_parent.lix OK
DELETE l_child.log OK
DELETE l_child.lix OK
COM ************************************
COM Fieldnames can be converted into new tables with a simple series of commands.
COM Open the table > SET LOG logname > DISPLAY > SET LOG > import LOG> DEFINE FIELDS
COM ************************************
OPEN %t_parent%
SET LOG l_parent
DISPLAY
SET LOG
IMPORT PRINT TO Temp_Parent "New_Field_Names.FIL" FROM "l_parent.LOG" SERVER 1 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 Temp_Parents
DEFINE FIELD f_field_name COMPUTED SUBSTR(SPLIT(full_record, " ", 1), 1, 33)
DEFINE FIELD f_field_type COMPUTED
"C" IF sub(ALL(SUB(FULL_Record,at(1," ",Full_record),100)),1,5) ="ASCII"
"D" IF sub(ALL(SUB(FULL_Record,at(1," ",Full_record),100)),1,5) ="DATE"
"N" IF sub(ALL(SUB(FULL_Record,at(1," ",Full_record),100)),1,5) ="ACL"
"N" IF sub(ALL(SUB(FULL_Record,at(1," ",Full_record),100)),1,5) ="NUMER"
"N" IF sub(ALL(SUB(FULL_Record,at(1," ",Full_record),100)),1,5) ="PRINT"
""
DEFINE FIELD f_field_Length COMPUTED split(all(sub(ALL(SUB(FULL_Record,at(1," ",Full_record),100)),14,10))," ",1)
DEFINE FIELD f_field_decimal COMPUTED split(all(sub(ALL(SUB(FULL_Record,at(1," ",Full_record),100)),14,10))," ",2)
******************************D03_Determine_Field_Length
T_child = SPLIT("%v_child_list%","|",%v_script_cnt%)
V_list_temp = ALL(v_list_length)
DELETE v_list_length OK
DELETE l_child.log OK
DELETE l_child.lix OK
OPEN %t_child%
COM ****************************************************************************
COM The SET LOG will capture the activity that is done while the log is open.
COM In this case we are using it to capture the DISPLAY. The DISPLAY is then
COM used to create a new table that contains the details of the DISPLAY command.
COM *****************************************************************************
SET LOG l_child
DISPLAY
SET LOG
IMPORT PRINT TO Temp_Child "New_Field_Names.FIL" FROM "l_child.LOG" SERVER 1 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 Temp_Parents
DEFINE FIELD f_field_name COMPUTED SUBSTR(SPLIT(full_record, " ", 1), 1, 33)
DEFINE FIELD f_field_Length COMPUTED split(all(sub(ALL(SUB(FULL_Record,at(1," ",Full_record),100)),14,10))," ",1)
EXTRACT f_field_name f_field_length TO temp_%v_script_cnt% OPEN
COM ******************************************************************
COM Creating a list of field lengths. This GROUP will compare the length of the current field
COM with the length of largest record for that field so far encountered. This will ensure that
COM when defining the field sizes for final extract that we are using the largest size--without
COM using an arbitary value such as 30 or 50.
COM *****************************************************************
v_list_length = blank(1000)
v_cnt = 1
GROUP
v_list_length = STRING(MAX(VALUE(f_field_length,0),VALUE(SPLIT("ALL(%v_list_temp%)","|",%v_cnt%),0)),3)+"|"+ ALLTRIM(v_list_length)
v_cnt = v_cnt + 1
END
v_list_length=ALL(v_list_length)
v_script_cnt = v_script_cnt + 1
**************************D04_Define_Parent
COM *************************************************************************
COM Captures the value of the length, ftype, fieldname, and decimal values from
COM the appropriate lists.
COM *************************************************************************
OPEN %T_Parent%
v_length = VALUE(split("%v_list_length%","|",%v_cnt_ctrl%),0)
v_ftype = SPLIT("%v_list_ftype%","|",%v_cnt_ctrl%)
v_field = SPLIT("%v_list%","|",%v_cnt_ctrl%)
v_decimal = value(split("%v_list_decimal%","|",%v_cnt_ctrl%),0)
COM ************************************************************************
COM Depending on the FTYPE of the various fields and other characteristics
COM the new field is created. NOTE the use of the F_ prefix before a DEFINEd FIELD
COM Best practices are to NEVER use fieldnames that might be utilized by a database.
COM The use of the F_ prefix insures that you can readily identify computed fields.
COM ***********************************************************************
IF FTYPE("%v_field%") = "D" ASSIGN v_temp = "%v_field% "
IF FTYPE("%v_field%") = "N" ASSIGN v_temp = "VALUE(STRING(%v_field%,%v_length%),%v_decimal%)"
IF FTYPE("%v_field%") = "C" ASSIGN v_temp = "SUB(ALL(%v_field%),1,%v_length%)"
v_extract = "%v_temp% as '%v_field%' " + alltrim(v_extract)
v_cnt_ctrl = v_cnt_ctrl +1
*****************************D05_Define_Child_a
T_Child1 = SPLIT("%v_child_list%","|",%v_script_cnt%)
OPEN %T_Child1%
v_cnt_ctrl = 1
v_extract = blank(1000)
DO D05a_Define_Child_b WHILE v_cnt_ctrl <= v_loop_ctrl
EXTRACT %v_extract% TO "%v_product%.fil" APPEND
EXTRACT %v_extract% TO "nEW rEPORT cHILDREN.fil" APPEND
v_script_cnt=v_script_cnt+1
*******************D05a_Define_child_b
COM ***********************************************************************
COM Based upon the FTYPE of the specific field AND the FTYPE of the
COM field in the parent file, the field is appropriately defined for consistency.
COM ************************************************************************
OPEN %T_Child1%
v_length = VALUE(split("%v_list_length%","|",%v_cnt_ctrl%),0)
v_ftype = SPLIT("%v_list_FTYPE%","|",%v_cnt_ctrl%)
v_field = SPLIT("%v_list%","|",%v_cnt_ctrl%)
v_decimal = value(split("%v_list_decimal%","|",%v_cnt_ctrl%),0)
DELETE f_%v_field% OK
IF FTYPE("%v_field%") = "D" AND v_ftype = "D" ASSIGN v_temp = "%v_field%"
IF FTYPE("%v_field%") = "N" AND v_ftype = "D" ASSIGN v_temp = "CTOD(ALL(%v_field%),'%v_date%')"
IF FTYPE("%v_field%") = "C" AND v_ftype = "D" ASSIGN v_temp = "CTOD(ALL(%v_field%),'%v_date%')"
IF FTYPE("%v_field%") = "U" AND v_ftype = "D" ASSIGN v_temp = "`19000101`"
IF FTYPE("%v_field%") = "D" AND v_ftype = "C" ASSIGN v_temp = "SUB(ALL(DATE(%v_field%)),1,%v_length%)"
IF FTYPE("%v_field%") = "N" AND v_ftype = "C" ASSIGN v_temp = "STRING(%v_field%,%v_length%)"
IF FTYPE("%v_field%") = "C" AND v_ftype = "C" ASSIGN v_temp = "SUB(ALL(%v_field%),1,%v_length%)"
IF FTYPE("%v_field%") = "U" AND v_ftype = "C" ASSIGN v_temp = "SUB('',1,%v_length%)"
IF FTYPE("%v_field%") = "D" AND v_ftype = "N" ASSIGN v_temp = "SUB(DATE(%v_field%),1,%v_length%)"
IF FTYPE("%v_field%") = "N" AND v_ftype = "N" ASSIGN v_temp = "VALUE(STRING(%v_field%,%v_length%),%v_decimal%)"
IF FTYPE("%v_field%") = "C" AND v_ftype = "N" ASSIGN v_temp = "VALUE(%v_field%,%v_decimal%)"
IF FTYPE("%v_field%") = "U" AND v_ftype = "N" ASSIGN v_temp = "VALUE('0.00',%v_decimal%)"
ASSIGN v_extract = "%v_temp% AS '%v_field%' " + alltrim(v_extract)
v_cnt_ctrl = v_cnt_ctrl +1
************************************D99_Clean_up
COM *********************************************************************
COM Deleting various fields, tables, and logs.
COM *********************************************************************
DELETE FORMAT TEMP_1 OK
DELETE TEMP_1.fil OK
DELETE FORMAT TEMP_child OK
DELETE TEMP_child.fil OK
DELETE FORMAT TEMP_parent1 OK
DELETE TEMP_parent1.fil OK
DELETE FORMAT TEMP_parent OK
DELETE TEMP_parent.fil OK
DELETE FORMAT temp_parent OK
DELETE temp_parent.FIL OK
DELETE l_parent.LOG OK
DELETE l_parent.lix OK
DELETE FORMAT temp_child OK
DELETE temp_child.FIL OK
DELETE l_child.LOG OK
DELETE l_child.lix OK
COM *********************************************************************
COM This section will delete the various TEMP_ tables that were created.
COM *********************************************************************
v_cnt = 2
DO D99f_Clean_up While v_cnt <= v_script_Loop
*********************D99f_clean_up
DELETE TEMP_%v_cnt%.fil OK
DELETE FORMAT Temp_%v_cnt% OK
v_cnt = v_cnt + 1
|
|