In this section: |
The following MODIFY enhancements were added in FOCUS 7.7.03
How to: Reference: |
You can use the MODIFY command to load data into a single segment fixed format sequential data source. This enables you to created fixed format files with specific data types that are difficult to create using the HOLD FORMAT INTERNAL command.
The data is loaded in the order in which it is input. Update and delete operations are not supported.
MODIFY FILE filename readcmd [COMPUTE field[/format1] = expression1;] [VALIDATE testfld[/format2] = expression2;] modify_select_and_load_cmds DATA [ON ddname] [VIA CRTFORM] END
where:
Is the name of the fixed format sequential file to be loaded.
Can be any supported MODIFY command for describing and reading incoming data such as CRTFORM, PROMPT, FREEFORM, or FIXFORM.
Can be an existing field in the data source, a temporary field, or an input field that requires calculations in order to be used in your MODIFY processing.
Is the format you assign to a temporary field. Omit the format for existing fields.
Is a temporary field that will be used to accept or reject a transaction based on a calculation. If the value of the field is zero, the transaction will be rejected.
Is the format you assign to the field. The format type must be numeric (I, F, D, or P. Specify the format only if you will use the field elsewhere in the request.
Are expressions supported in MODIFY.
Can be any sequence of MODIFY commands that select transactions, such as MATCH and NEXT, as well as INCLUDE commands that load records into the data source.
Specifies the logical name of a data source that contains the transaction records.
For complete information about MODIFY facilities and commands, see your MODIFY documentation.
The following Master File describes a fixed format data source called CENTFIX. This Master File is based on the Master File for the FOCUS data source named CENTINV, with the SUFFIX changed to FIX, the SEGTYPE changed to S0, and the DEFINE fields removed:
FILE=CENTFIX, SUFFIX=FIX, FDFC=19, FYRT=00 SEGNAME=INVINFO, SEGTYPE=S0, $ FIELD=PROD_NUM, ALIAS=PNUM, FORMAT=A4, INDEX=I, TITLE='Product,Number:', DESCRIPTION='Product Number', $ FIELD=PRODNAME, ALIAS=PNAME, FORMAT=A30, WITHIN=PRODCAT, TITLE='Product,Name:', DESCRIPTION='Product Name', $ FIELD=QTY_IN_STOCK, ALIAS=QIS, FORMAT=I7, TITLE='Quantity,In Stock:', DESCRIPTION='Quantity In Stock', $ FIELD=PRICE, ALIAS=RETAIL, FORMAT=D10.2, TITLE='Price:', DESCRIPTION=Price, $ FIELD=COST, ALIAS=OUR_COST, FORMAT=D10.2, TITLE='Our,Cost:', DESCRIPTION='Our Cost:', $
The following procedure loads the fixed format sequential file named CENTFIX. The input data is the same as the data used to load the FOCUS data source CENTINV. However, the record for PROD_NUM '1022' has been moved out of sequence, in front of the record for PROD_NUM '1020':
CMS FILEDEF CENTFIX DISK CENTFIX DATA A CREATE FILE CENTFIX MODIFY FILE CENTFIX FIXFORM PROD_NUM/4 PRODNAME/30 QTY_IN_STOCK/7 PRICE/12 COST/12 CHECK OFF MATCH PROD_NUM ON MATCH REJECT ON NOMATCH INCLUDE DATA 10042 Hd VCR LCD Menu 43068 179.00 129.00 1006Combo Player - 4 Hd VCR + DVD 13527 399.00 289.00 1008DVD Upgrade Unit for Cent. VCR 199 199.00 139.00 1010750SL Digital Camcorder 300 X 10758 999.00 750.00 1012650DL Digital Camcorder 150 X 2972 899.00 710.00 1014340SX Digital Camera 65K P 990 249.00 199.00 1016330DX Digital Camera 1024K P 12707 279.00 199.00 1018250 8MM Camcorder 40 X 60073 399.00 320.00 1022120 VHS-C Camcorder 40 X 2300 399.00 259.00 1020150 8MM Camcorder 20 X 5961 319.00 240.00 1024110 VHS-C Camcorder 20 X 4000 349.00 249.00 1026AR3 35MM Camera 10 X 12444 129.00 95.00 1028AR2 35MM Camera 8 X 11499 109.00 79.00 1030QX Portable CD Player 22000 169.00 99.00 1032R5 Micro Digital Tape Recorder 1990 89.00 69.00 1034ZT Digital PDA - Commercial 21000 499.00 349.00 1036ZC Digital PDA - Standard 33000 299.00 249.00 END
The following messages indicate that the data was loaded:
CENTFIX FIX ON 07/14/2006 AT 10.17.05 (FOC1291) RECORDS AFFECTED DURING CURRENT REQUEST : 17/INSERT TRANSACTIONS: TOTAL = 17 ACCEPTED= 17 REJECTED= 0 SEGMENTS: INPUT = 17 UPDATED = 0 DELETED = 0
The following request reports from the CENTFIX fixed format file:
CMS FILEDEF CENTFIX DISK c:\ibi\apps\baseapp\centfix.ftm CENTFIX DATA A TABLE FILE CENTFIX SUM PRODNAME PRICE COST BY PROD_NUM END
The output is:
Product Product Our Number: Name: Price: Cost: ------- ------- ------ ----- 1004 2 Hd VCR LCD Menu 179.00 129.00 1006 Combo Player - 4 Hd VCR + DVD 399.00 289.00 1008 DVD Upgrade Unit for Cent. VCR 199.00 139.00 1010 750SL Digital Camcorder 300 X 999.00 750.00 1012 650DL Digital Camcorder 150 X 899.00 710.00 1014 340SX Digital Camera 65K P 249.00 199.00 1016 330DX Digital Camera 1024K P 279.00 199.00 1018 250 8MM Camcorder 40 X 399.00 320.00 1020 150 8MM Camcorder 20 X 319.00 240.00 1022 120 VHS-C Camcorder 40 X 399.00 259.00 1024 110 VHS-C Camcorder 20 X 349.00 249.00 1026 AR3 35MM Camera 10 X 129.00 95.00 1028 AR2 35MM Camera 8 X 109.00 79.00 1030 QX Portable CD Player 169.00 99.00 1032 R5 Micro Digital Tape Recorder 89.00 69.00 1034 ZT Digital PDA - Commercial 499.00 349.00 1036 ZC Digital PDA - Standard 299.00 249.00
The CENTFIX data source was loaded with the record for PROD_NUM '1022' before the record for PROD_NUM '1020'. This does not cause a problem for reporting if the SEGTYPE is S0.
However, if the SEGTYPE for CENTFIX is S1, PROD_NUM is considered the key field, and is a candidate for keyed retrieval. Keyed retrieval assumes that the records are in order of the key field. If a value higher than the requested value is found in the data source, it is assumed that no record exists with the requested value, and retrieval halts.
Note: The SEGTYPE would be S1 if CENTFIX had been created as a HOLD file based on the CENTINV data source, and if the Master File had not been manually edited to change the SEGTYPE value.
Consider the following request that prints the record in which PROD_NUM equals '1020' and in which FIXRETRIEVE is ON (the default value):
TABLE FILE CENTFIX PRINT PROD_NUM PRODNAME PRICE COST WHERE PROD_NUM EQ '1020' ON TABLE SET FIXRETRIEVE ON END
The equality test does not retrieve the record with PROD_NUM='1020' because the keyed retrieval assumes the records are in order of the key field and stops searching when the record for PROD_NUM '1022' is found:
NUMBER OF RECORDS IN TABLE= 0 LINES= 0
Running the request with FIXRETRIEVE OFF retrieves the requested record even though it is out of sequence:
TABLE FILE CENTFIX PRINT PROD_NUM PRODNAME PRICE COST WHERE PROD_NUM EQ '1020' ON TABLE SET FIXRETRIEVE OFF END
The output is:
Product Product Our Number: Name: Price: Cost: ------- ------- ------ ----- 1020 150 8MM Camcorder 20 X 319.00 240.00
How to: |
MODIFY FIXFORM can load multiple text fields into a data source. The data source must have a Write data adapter that supports MODIFY, and the data source must support text fields. In the case of Relational data sources, long varchar fields are mapped as text fields in the Master File. Support for this feature with a relational data adapter is dependent on whether the specific relational engine supports multiple long varchar columns.
The text fields must be the last fields in the FIXFORM field list and may not be conditional transaction fields. In the file, each text field must be terminated by a %$ character combination on a line by itself.
If the data to be loaded is from a HOLD file, the text fields must also be the last fields in the HOLD file. However, the text fields can be loaded anywhere in the receiving data source.
FIXFORM field1/fmt1 ... fieldn/fmtntxtfld1/TX ... txtfldn/TX
where:
Are non-text fields.
Are the format specifications for the non-text fields.
Are text fields.
Is the format specification for the text fields.
FIXFORM FROM ddname
where:
Is the AS name used to create the HOLD file. If no AS phrase was specified in the HOLD command, the ddname is HOLD.
This example uses the COURSES data source, which contains a text field, to create a HOLD file with three text fields. It then uses this HOLD file to load data into the TEXT3 data source.
The following request creates the HOLD file:
SET ASNAMES=ON TABLE FILE COURSES PRINT COURSE_CODE DESCRIPTION AS 'DESCRIPTION1' DESCRIPTION/TX25 AS 'DESCRIPTION2' DESCRIPTION/TX100 AS 'DESCRIPTION3' ON TABLE HOLD FORMAT ALPHA END
The following is the Master File for the TEXT3 data source:
FILE=TEXT3 ,SUFFIX=FOC SEGNAME=SEG1,SEGTYPE=S1 FIELDNAME =COURSE_CODE ,CCODE ,A6 ,$ FIELDNAME =DESCRIPTION1 ,DESC1 ,TX50 ,$ FIELDNAME =DESCRIPTION2 ,DESC2 ,TX25 ,$ FIELDNAME =DESCRIPTION3 ,DESC3 ,TX100 ,$
The following procedure loads the TEXT3 data source:
CREATE FILE TEXT3 MODIFY FILE TEXT3 FIXFORM FROM CRSEHOLD DATA ON CRSEHOLD END
The following messages indicate that the data was loaded:
NEW FILE TEXT3 FOCUS A1 ON 06/04/2004 AT 16.02.18 > TEXT3 FOCUS A1 ON 06/04/2004 AT 16.02.18 TRANSACTIONS: TOTAL = 3 ACCEPTED= 3 REJECTED= 0 SEGMENTS: INPUT = 3 UPDATED = 0 DELETED = 0 >
The following request prints the second text field from the data source:
TABLE FILE TEXT3 PRINT DESCRIPTION2 BY COURSE_CODE ON COURSE_CODE SKIP-LINE END
The output is:
COURSE_CODE DESCRIPTION2
----------- ------------
101 This course provides the
DP professional with the
skills needed to create,
maintain, and report from
FOCUS databases.
200 Anyone responsible for
designing FOCUS databases
will benefit from this
course, which provides
the skills needed to
design large, complex
databases and tune
existing ones.
201 This is a course in FOCUS
efficiencies.
How to: Reference: |
In MODIFY, by default, FIXFORM FROM mastername treats all transaction data as conditional, meaning that space-filled fields are considered not present, and as such cannot be updated or used in updates.
The SET FIXFRMINPUT command enables you to specify how to handle FIXFORM input fields as either conditional (field/format C) or non-conditional fields. Thus, spaces in a transaction field can be used for updating database fields.
SET FIXFRMINPUT = {COND|NONCOND}
where:
Treats all transaction fields generated by FIXFORM FROM mastername as conditional (format C) fields. COND is the default value.
Treats all transaction fields as present in the transaction, and their contents are treated as real values.
Note that if you have not changed the value of the FIXFRMINPUT parameter and you query its value, the value displays as DEFAULT.
The following procedure establishes a transaction file, defining LN1 in HOLD file TRANS to be blank for PIN 000000040.
SET ASNAMES = ON DEFINE FILE EMPDATA LN1/A15 = IF PIN EQ '000000040' THEN '' ELSE LN; END TABLE FILE EMPDATA PRINT PIN LN1 AS LN IF PIN FROM '000000010' TO '000000100' ON TABLE HOLD AS TRANS END
The following procedure, sets the FIXFORM FROM input fields as conditional (the default) and reports on the output from the MODIFY:
SET FIXFRMINPUT = COND -? SET FIXFRMINPUT &FIXF MODIFY FILE EMPDATA FIXFORM FROM TRANS MATCH PIN ON MATCH UPDATE LN ON NOMATCH REJECT DATA ON TRANS END TABLE FILE EMPDATA HEADING " " "VALUE OF FIXFRMINPUT IS &FIXF " " " PRINT PIN LN IF PIN FROM '000000010' TO '000000100' END
The output shows that the blank in the transaction file was not used to update the last name in the data source:
VALUE OF FIXFRMINPUT IS COND PIN LASTNAME --- -------- 000000010 VALINO 000000020 BELLA 000000030 CASSANOVA 000000040 ADAMS 000000050 ADDAMS 000000060 PATEL 000000070 SANCHEZ 000000080 SO 000000090 PULASKI 000000100 ANDERSON
The following procedure sets the FIXFORM FROM input fields as non-conditional and reports on the output from the MODIFY:
SET FIXFRMINPUT = NONCOND -? SET FIXFRMINPUT &FIXF MODIFY FILE EMPDATA FIXFORM FROM TRANS MATCH PIN ON MATCH UPDATE LN ON NOMATCH REJECT DATA ON TRANS END TABLE FILE EMPDATA HEADING " " "VALUE OF FIXFRMINPUT IS &FIXF " " " PRINT PIN LN IF PIN FROM '000000010' TO '000000100' END
The output shows that the last name for PIN 000000040 has been updated to contain blanks:
VALUE OF FIXFRMINPUT IS NONCOND PIN LASTNAME --- -------- 000000010 VALINO 000000020 BELLA 000000030 CASSANOVA 000000040 000000050 ADDAMS 000000060 PATEL 000000070 SANCHEZ 000000080 SO 000000090 PULASKI 000000100 ANDERSON
| Information Builders |