The following features were added as of FOCUS 7.7.03.
|
How to: |
The LCWORD3 function converts the letters in a character string to mixed-case by converting the first letter of each word to uppercase and converting every other letter to lowercase. In addition, a single quotation mark indicates that the next letter should be converted to uppercase as long as it is neither followed by a blank nor the last character in the input string.
For example, 'SMITH' would be changed to 'Smith' and JACK'S would be changed to Jack's.
LCWORD3(length, string, output)
where:
Integer
Is the length in characters of the character string or field to be converted, or a field that contains the length.
Alphanumeric
Is the character string to be converted, or a field that contains the string.
Alphanumeric
Is the name of the field that contains the result, or the format of the output value enclosed in single quotation marks. The length must be greater than or equal to the length of length.
LCWORD3 converts the strings O'CONNOR’s and o’connor’s to mixed-case:
DEFINE FILE EMPLOYEE MYVAL1/A10='O'CONNOR'S'; MYVAL2/A10='o'connor's'; LC1/A10 = LCWORD3(10, MYVAL1, 'A10'); LC2/A10 = LCWORD3(10, MYVAL2, 'A10'); END TABLE FILE EMPLOYEE SUM LAST_NAME NOPRINT MYVAL1 LC1 MYVAL2 LC2 END
On the output, the letter C after the first single quotation mark is in uppercase because it is not followed by a blank and is not the final letter in the input string. The letter s after the second single quotation mark is in lowercase because it is the last character in the input string:
MYVAL1 LC1 MYVAL2 LC2 ------ --- ------ --- O'CONNOR'S O'Connor's o'connor's O'Connor's
|
How to: |
The FIYR function returns the financial year, also known as the fiscal year, corresponding to a given calendar date based on the financial year starting date and the financial year numbering convention.
FIYR(inputdate, lowcomponent, startmonth, startday, yrnumbering, output)
where:
Date
Is the date for which the financial year is returned. The date must be a standard date stored as an offset from the base date.
If the financial year does not begin on the first day of a month, the date must have Y(Y), M, and D components, or Y(Y) and JUL components (note that JUL is equivalent to YJUL). Otherwise, the date only needs Y(Y) and M components or Y(Y) and Q components.
Alphanumeric
Is one of the following:
Numeric
1 through 12 are used to represent the starting month of the financial year, where 1 represents January and 12 represents December. If the low component is Q, the start month must be 1, 4, 7, or 10.
Numeric
Is the starting day of the starting month, usually 1. If the low component is M or Q, 1 is required.
Alphanumeric
Valid values are:
FYE to specify the Financial Year Ending convention. The financial year number is the calendar year of the ending date of the financial year. For example, when the financial year starts on October 1, 2008, the date 2008 November 1 is in FY 2009 Q1 because that date is in the financial year that ends on 2009 September 30.
FYS to specify the Financial Year Starting convention. The financial year number is the calendar year of the starting date of the financial year. For example, when the financial year starts on April 6, 2008, the date 2008 July 6 is in FY 2008 Q2 because that date is in the financial year that starts on 2008 April 6.
I, Y, or YY
The result will be in integer format, or Y or YY. This function returns a year value. In case of an error, zero is returned.
Note: February 29 cannot be used as a start day for a financial year.
The following request against the CENTSTMT data source obtains the financial year corresponding to an account period (field PERIOD, format YYM) and returns the values in each of the supported formats: Y, YY, and I4.
DEFINE FILE CENTSTMT FISCALYY/YY=FIYR(PERIOD,'M', 4,1,'FYE',FISCALYY); FISCALY/Y=FIYR(PERIOD,'M', 4,1,'FYE',FISCALY); FISCALI/I4=FIYR(PERIOD,'M', 4,1,'FYE',FISCALI); END TABLE FILE CENTSTMT PRINT PERIOD FISCALYY FISCALY FISCALI BY GL_ACCOUNT WHERE GL_ACCOUNT LT '2100' END
On the output, note that the period April 2002 (2002/04) is in fiscal year 2003 because the starting month is April (4), and the FYE numbering convention is used:
Ledger
Account PERIOD FISCALYY FISCALY FISCALI
------- ------ -------- ------- -------
1000 2002/01 2002 02 2002
2002/02 2002 02 2002
2002/03 2002 02 2002
2002/04 2003 03 2003
2002/05 2003 03 2003
2002/06 2003 03 2003
2000 2002/01 2002 02 2002
2002/02 2002 02 2002
2002/03 2002 02 2002
2002/04 2003 03 2003
2002/05 2003 03 2003
2002/06 2003 03 2003|
How to: |
The FIQTR function returns the financial quarter corresponding to a given calendar date based on the financial year starting date and the financial year numbering convention.
FIQTR(inputdate, lowcomponent, startmonth, startday, yrnumbering, output)
where:
Date
Is the date for which the financial year is returned. The date must be a standard date stored as an offset from the base date.
If the financial year does not begin on the first day of a month, the date must have Y(Y), M, and D components, or Y(Y) and JUL components (note that JUL is equivalent to YJUL). Otherwise, the date only needs Y(Y) and M components or Y(Y) and Q components.
Alphanumeric
Is one of the following:
Numeric
1 through 12 are used to represent the starting month of the financial year, where 1 represents January and 12 represents December. If the low component is Q, the start month must be 1, 4, 7, or 10.
Numeric
Is the starting day of the starting month, usually 1. If the low component is M or Q, 1 is required.
Alphanumeric
Valid values are:
FYE to specify the Financial Year Ending convention. The financial year number is the calendar year of the ending date of the financial year. For example, when the financial year starts on October 1, 2008, the date 2008 November 1 is in FY 2009 Q1 because that date is in the financial year that ends on 2009 September 30.
FYS to specify the Financial Year Starting convention. The financial year number is the calendar year of the starting date of the financial year. For example, when the financial year starts on April 6, 2008, the date 2008 July 6 is in FY 2008 Q2 because that date is in the financial year that starts on 2008 April 6.
I or Q
The result will be in integer format, or Q. This function will return a value of 1 through 4. In case of an error, zero is returned.
Note: February 29 cannot be used as a start day for a financial year.
The following request against the CENTHR data source obtains the financial quarter corresponding to an employee starting date (field START_DATE, format YYMD) and returns the values in each of the supported formats: Q and I1.
DEFINE FILE CENTHR FISCALQ/Q=FIQTR(START_DATE,'D',10,1,'FYE',FISCALQ); FISCALI/I1=FIQTR(START_DATE,'D',10,1,'FYE',FISCALI); END TABLE FILE CENTHR PRINT START_DATE FISCALQ FISCALI BY LNAME BY FNAME WHERE LNAME LIKE 'C%' END
On the output, note that the date November 12, 1998 (1998/11/12) is in fiscal quarter Q1 because the starting month is October (10):
Last First Starting Name Name Date FISCALQ FISCALI ---- ----- -------- ------- ------- CHARNEY ROSS 1998/09/12 Q4 4 CHIEN CHRISTINE 1997/10/01 Q1 1 CLEVELAND PHILIP 1996/07/30 Q4 4 CLINE STEPHEN 1998/11/12 Q1 1 COHEN DANIEL 1997/10/05 Q1 1 CORRIVEAU RAYMOND 1997/12/05 Q1 1 COSSMAN MARK 1996/12/19 Q1 1 CRONIN CHRIS 1996/12/03 Q1 1 CROWDER WESLEY 1996/09/17 Q4 4 CULLEN DENNIS 1995/09/05 Q4 4 CUMMINGS JAMES 1993/07/11 Q4 4 CUTLIP GREGG 1997/03/26 Q2 2
|
How to: |
The FIYYQ function returns a financial date containing both the financial year and quarter that corresponds to a given calendar date. The returned financial date is based on the financial year starting date and the financial year numbering convention.
FIYYQ(inputdate, lowcomponent, startmonth, startday, yrnumbering, output)
where:
Date
Is the date for which the financial year is returned. The date must be a standard date stored as an offset from the base date.
If the financial year does not begin on the first day of a month, the date must have Y(Y), M, and D components, or Y(Y) and JUL components (note that JUL is equivalent to YJUL). Otherwise, the date only needs Y(Y) and M components or Y(Y) and Q components.
Alphanumeric
Is one of the following:
Numeric
1 through 12 are used to represent the starting month of the financial year, where 1 represents January and 12 represents December. If the low component is Q, the start month must be 1, 4, 7, or 10.
Numeric
Is the starting day of the starting month, usually 1. If the low component is M or Q, 1 is required.
Alphanumeric
Valid values are:
FYE to specify the Financial Year Ending convention. The financial year number is the calendar year of the ending date of the financial year. For example, when the financial year starts on October 1, 2008, the date 2008 November 1 is in FY 2009 Q1 because that date is in the financial year that ends on 2009 September 30.
FYS to specify the Financial Year Starting convention. The financial year number is the calendar year of the starting date of the financial year. For example, when the financial year starts on April 6, 2008, the date 2008 July 6 is in FY 2008 Q2 because that date is in the financial year that starts on 2008 April 6.
Y[Y]Q or QY[Y]
In case of an error, zero is returned.
Note: February 29 cannot be used as a start day for a financial year.
The following request against the CENTHR data source converts each employee starting date (field START_DATE, format YYMD) to a financial date containing year and quarter components in all the supported formats: YQ, YYQ, QY, and QYY.
DEFINE FILE CENTHR FISYQ/YQ=FIYYQ(START_DATE,'D',10,1,'FYE',FISYQ); FISYYQ/YYQ=FIYYQ(START_DATE,'D',10,1,'FYE',FISYYQ); FISQY/QY=FIYYQ(START_DATE,'D',10,1,'FYE',FISQY); FISQYY/QYY=FIYYQ(START_DATE,'D',10,1,'FYE',FISQYY); END TABLE FILE CENTHR PRINT START_DATE FISYQ FISYYQ FISQY FISQYY BY LNAME BY FNAME WHERE LNAME LIKE 'C%' END
On the output, note that the date November 12, 1998 (1998/11/12) is converted to Q1 1999 because the starting month is October (10), and the FYE numbering convention is used:
Last First Starting Name Name Date FISYQ FISYYQ FISQY FISQYY ---- ----- -------- ----- ------ ----- ------ CHARNEY ROSS 1998/09/12 98 Q4 1998 Q4 Q4 98 Q4 1998 CHIEN CHRISTINE 1997/10/01 98 Q1 1998 Q1 Q1 98 Q1 1998 CLEVELAND PHILIP 1996/07/30 96 Q4 1996 Q4 Q4 96 Q4 1996 CLINE STEPHEN 1998/11/12 99 Q1 1999 Q1 Q1 99 Q1 1999 COHEN DANIEL 1997/10/05 98 Q1 1998 Q1 Q1 98 Q1 1998 CORRIVEAU RAYMOND 1997/12/05 98 Q1 1998 Q1 Q1 98 Q1 1998 COSSMAN MARK 1996/12/19 97 Q1 1997 Q1 Q1 97 Q1 1997 CRONIN CHRIS 1996/12/03 97 Q1 1997 Q1 Q1 97 Q1 1997 CROWDER WESLEY 1996/09/17 96 Q4 1996 Q4 Q4 96 Q4 1996 CULLEN DENNIS 1995/09/05 95 Q4 1995 Q4 Q4 95 Q4 1995 CUMMINGS JAMES 1993/07/11 93 Q4 1993 Q4 Q4 93 Q4 1993 CUTLIP GREGG 1997/03/26 97 Q2 1997 Q2 Q2 97 Q2 1997
|
How to: |
|
Reference: |
The DBASOURCE parameter determines which security attributes are used to grant access to multi-file structures. By default, access restrictions are based on the host file in a JOIN structure or the last file in a COMBINE structure. If you set the DBASOURCE parameter to ALL, access restrictions from all files in a JOIN or COMBINE structure will be enforced.
Note: You can also create and implement a DBAFILE to contain and enforce the access restrictions from all files in a JOIN or COMBINE structure. For information about using a central Master File to contain access restrictions, see the Describing Data manual.
The SET DBASOURCE command can only be issued one time in a session or connection. Any attempt to issue the command additional times will be ignored. If the value is set in a profile such as FOCPARM, no user can change it at any point in the session.
When DBASOURCE=ALL:
When DBASOURCE=HOST:
SET DBASOURCE = {HOST|ALL}where:
Enforces access restrictions only from the host file in a JOIN structure or the last file in a COMBINE structure unless a DBAFILE is used to enforce access restrictions to other files in the structure. HOST is the default value.
Requires the user to have read access to every file in a JOIN or COMBINE structure. The user needs W, U, or RW access to a file in a COMBINE structure when an INCLUDE, UPDATE, or DELETE command is issued against that file.
(FOC32575) DBASOURCE CANNOT BE RESET VALUE WAS NOT CHANGED
The following request joins the TRAINING data source to the EMPDATA and COURSE data sources and then issues a request against the joined structure:
JOIN CLEAR *
JOIN COURSECODE IN TRAINING TO COURSECODE IN COURSE AS J1
JOIN PIN IN TRAINING TO PIN IN EMPDATA AS J2
TABLE FILE TRAINING
PRINT COURSECODE AS 'CODE' CTITLE
LOCATION AS 'LOC'
BY LASTNAME
WHERE COURSECODE NE ' '
WHERE LOCATION EQ 'CA' OR LOCATION LIKE 'N%'
ENDWhen the Master Files do not have DBA attributes, the output is:
LASTNAME CODE CTITLE LOC
-------- ---- ------ ---
ADAMS EDP750 STRATEGIC MARKETING PLANNING NJ
CASTALANETTA EDP130 STRUCTURED SYS ANALYSIS WKSHP NY
AMA130 HOW TO WRITE USERS MANUAL CA
CHISOLM EDP690 APPLIED METHODS IN MKTG RESEARCH NJ
FERNSTEIN MC90 MANAGING DISTRIBUTOR SALE NETWORK NY
GORDON SFC280 FUND OF ACCTG FOR SECRETARIES NY
LASTRA MC90 MANAGING DISTRIBUTOR SALE NETWORK NY
MARTIN EDP130 STRUCTURED SYS ANALYSIS WKSHP CA
MEDINA EDP690 APPLIED METHODS IN MKTG RESEARCH NJ
OLSON PU168 FUNDAMENTALS OF MKTG COMMUNICATIONS NY
RUSSO PU168 FUNDAMENTALS OF MKTG COMMUNICATIONS NY
SO BIT420 EXECUTIVE COMMUNICATION CA
WANG PU440 GAINING COMPETITIVE ADVANTAGE NY
WHITE BIT420 EXECUTIVE COMMUNICATION CANow add the following DBA attributes to the bottom of the TRAINING Master File:
END DBA = DBA1,$ USER = TUSER, ACCESS =R,$
Running the same request produces the following message:
(FOC047) THE USER DOES NOT HAVE SUFFICIENT ACCESS RIGHTS TO THE FILE: TRAINING BYPASSING TO END OF COMMAND
Now issue the following SET PASS command:
SET PASS = TUSER
Add the following DBA attributes to the bottom of the COURSE Master File:
END DBA = DBA1,$ USER = CUSER, ACCESS = R,$
Add the following DBA attributes to the bottom of the EMPDATA Master File:
END DBA = DBA1,$ USER = EUSER, ACCESS = R,$
Note that the DBA attribute has the same value in all of the Master Files.
Now run the request again. There will be no security violation, and the report output will be generated. Since the DBASOURCE parameter is set to HOST (the default), you can run the request using a password that is valid only in the host file.
Now set the DBASOURCE parameter to ALL:
SET DBASOURCE = ALL SET PASS = TUSER
Running the request produces the following message because TUSER is not a valid user for the COURSE data source:
(FOC052) THE USER DOES NOT HAVE ACCESS TO THE FIELD: CTITLE
Now issue the following SET PASS command that sets a valid password for each file in the structure:
SET PASS = TUSER IN TRAINING, CUSER IN COURSE, EUSER IN EMPDATA
You can now run the request and generate the report output.
Once SET DBASOURCE command has been issued, its value cannot be changed. The following SET command attempts to change the value to HOST, but the query command output shows that it was not changed:
> > set dbasource = host (FOC32575) DBASOURCE CANNOT BE RESET VALUE WAS NOT CHANGED
|
Reference: |
On certain types of reports, negative values are displayed without a minus sign (-) or any other indication of a negative value.
In order to suppress the display of a negative indicator on FOCUS report output, you can use the A option with any numeric format in a DEFINE or COMPUTE command or in the USAGE format for a field in a Master File. This option affects only the display, not the stored value. To calculate the absolute value, use the ABS function.
The following request against the GGSALES data source sums budgeted dollars and sales dollars and then calculates the difference twice, once in the DIFF field, which has a format that displays minus signs (I8), and once in the SDIFF field, which has a format that suppresses minus signs (I8A).
DEFINE FILE GGSALES DIFF/I8 = BUDDOLLARS - DOLLARS; SDIFF/I8A = BUDDOLLARS - DOLLARS; END TABLE FILE GGSALES SUM DOLLARS BUDDOLLARS DIFF SDIFF BY PRODUCT ON TABLE COLUMN-TOTAL END
The DIFF column displays a minus sign (-) with negative numbers. The SDIFF column suppresses the minus sign (-). Note that the signed values are used to calculate the column total.
Product Dollar Sales Budget Dollars DIFF SDIFF ------- ------------ -------------- ---- ----- Biscotti 5263317 5290134 26817 26817 Capuccino 2381590 2395456 13866 13866 Coffee Grinder 2337567 2265996 -71571 71571 Coffee Pot 2449585 2472131 22546 22546 Croissant 7749902 7824715 74813 74813 Espresso 3906243 3904540 -1703 1703 Latte 10943622 10993890 50268 50268 Mug 4522521 4547785 25264 25264 Scone 4216114 4152311 -63803 63803 Thermos 2385829 2373820 -12009 12009 TOTAL 46156290 46220778 64488 64488
The following example adds a HOLD command to the request.
DEFINE FILE GGSALES DIFF/I8 = BUDDOLLARS - DOLLARS; SDIFF/I8A = BUDDOLLARS - DOLLARS; END TABLE FILE GGSALES SUM DOLLARS BUDDOLLARS DIFF SDIFF BY PRODUCT ON TABLE COLUMN-TOTAL ON TABLE HOLD AS NEG1 FORMAT ALPHA END
The following values are propagated to the NEG1 HOLD file. Notice that the last column, which represents the SDIFF field, contains signed negative values.
Biscotti 5263317 5290134 26817 26817 Capuccino 2381590 2395456 13866 13866 Coffee Grinder 2337567 2265996 -71571 -71571 Coffee Pot 2449585 2472131 22546 22546 Croissant 7749902 7824715 74813 74813 Espresso 3906243 3904540 -1703 -1703 Latte 1094362210993890 50268 50268 Mug 4522521 4547785 25264 25264 Scone 4216114 4152311 -63803 -63803 Thermos 2385829 2373820 -12009 -12009
The USAGE attribute for the SDIFF field in the NEG1 Master File specifies the A option so that a report against the HOLD file will suppress negative signs for that field.
FILENAME=NEG1, SUFFIX=FIX , IOTYPE=STREAM, $
SEGMENT=NEG1, SEGTYPE=S1, $
FIELDNAME=PRODUCT, ALIAS=E01, USAGE=A16, ACTUAL=A16, $
FIELDNAME=DOLLARS, ALIAS=E02, USAGE=I08, ACTUAL=A08, $
FIELDNAME=BUDDOLLARS, ALIAS=E03, USAGE=I08, ACTUAL=A08, $
FIELDNAME=DIFF, ALIAS=E04, USAGE=I8, ACTUAL=A08, $
FIELDNAME=SDIFF, ALIAS=E05, USAGE=I8A, ACTUAL=A08, $|
How to: |
|
Reference: |
The FPRINT function converts any type of field, except for a text field, to its alphanumeric equivalent for display. The alphanumeric representation will include any display options that are specified in the format of the original field.
FPRINT(infield, 'usageformat', outfield)
where:
Is the field to be converted.
Is the usage format of the field to be converted, including display options. The format must be enclosed in single quotation marks.
Is the name of the output field or its format enclosed in single quotation marks. The output field must be long enough to hold the converted number itself, with a sign and decimal point, plus any additional characters generated by display options, such as commas, a currency symbol, or a percent sign.
For example, D12.2 format is converted to A14 because it outputs two decimal digits, a decimal point, a possible minus sign (-), up to eight integer digits, and two commas. If the output format is not large enough, excess right-hand characters are truncated.
The following request against the EMPLOYEE data source uses FPRINT to convert the CURR_SAL, ED_HRS, and BANK_ACCT fields to alphanumeric for display on the report output. Then, the STRREP function replaces the blanks in the alphanumeric representation of CURR_SAL with asterisks. CURR_SAL has format D12.2M, so the alphanumeric representation has format A15. The ED_HRS field has format F6.2, so the alphanumeric representation has format A6. The BANK_ACCT field has format I9S, so the alphanumeric representation has format A9. The alphanumeric representations of the numeric fields are right-aligned. The /R options in the PRINT command cause the column titles to be right-aligned over the values.
DEFINE FILE EMPLOYEE ASAL/A15 = FPRINT(CURR_SAL, 'D12.2M', ASAL); ASAL/A15 = STRREP(15, ASAL, 1, ' ', 1, '*', 15, ASAL); AED/A6 = FPRINT(ED_HRS, 'F6.2', AED); ABANK/A9 = FPRINT(BANK_ACCT, 'I9S', ABANK); END TABLE FILE EMPLOYEE PRINT CURR_SAL ASAL ED_HRS AED/R BANK_ACCT ABANK/R WHERE BANK_NAME NE ' ' ON TABLE SET PAGE NOPAGE END
The output is:
CURR_SAL ASAL ED_HRS AED BANK_ACCT ABANK -------- ---- ------ ------ --------- --------- $18,480.00 *****$18,480.00 50.00 50.00 40950036 40950036 $29,700.00 *****$29,700.00 .00 .00 160633 160633 $26,862.00 *****$26,862.00 30.00 30.00 819000702 819000702 $21,780.00 *****$21,780.00 75.00 75.00 122850108 122850108 $16,100.00 *****$16,100.00 50.00 50.00 136500120 136500120 $27,062.00 *****$27,062.00 45.00 45.00 163800144 163800144
The following request against the EMPLOYEE data source converts the HIRE_DATE field to alphanumeric format. It also creates an alphanumeric date field named ADATE and converts it to its alphanumeric representation. The HIRE_DATE field has format I6YMD and the ADATE field has format A6YMD, so the alphanumeric representations have format A8 to account for the slashes between the date components. The /R option right-aligns the column titles over the field values.
DEFINE FILE EMPLOYEE AHDATE/A8 = FPRINT(HIRE_DATE,'I6YMD', AHDATE); ADATE/A6YMD = EDIT(HIRE_DATE); AADATE/A8 = FPRINT(ADATE,'A6YMD', AADATE); END TABLE FILE EMPLOYEE PRINT HIRE_DATE AHDATE/R ADATE AADATE/R ON TABLE SET PAGE NOPAGE END
The output is:
HIRE_DATE AHDATE ADATE AADATE --------- -------- ----- -------- 80/06/02 80/06/02 80/06/02 80/06/02 81/07/01 81/07/01 81/07/01 81/07/01 82/05/01 82/05/01 82/05/01 82/05/01 82/01/04 82/01/04 82/01/04 82/01/04 82/08/01 82/08/01 82/08/01 82/08/01 82/01/04 82/01/04 82/01/04 82/01/04 82/07/01 82/07/01 82/07/01 82/07/01 81/07/01 81/07/01 81/07/01 81/07/01 82/04/01 82/04/01 82/04/01 82/04/01 82/02/02 82/02/02 82/02/02 82/02/02 82/04/01 82/04/01 82/04/01 82/04/01 81/11/02 81/11/02 81/11/02 81/11/02
The following request against the VIDEOTRK data source converts the TRANSDATE (YMD) field to alphanumeric format. The alphanumeric representation has format A8 to account for the slashes between the date components.
DEFINE FILE VIDEOTRK ALPHA_DATE/A8 = FPRINT(TRANSDATE,'YMD', ALPHA_DATE); END TABLE FILE VIDEOTRK PRINT TRANSDATE ALPHA_DATE WHERE TRANSDATE LE '91/06/20' ON TABLE SET PAGE NOPAGE END
The output is:
TRANSDATE ALPHA_DATE --------- ---------- 91/06/20 91/06/20 91/06/19 91/06/19 91/06/19 91/06/19 91/06/18 91/06/18 91/06/19 91/06/19 91/06/18 91/06/18 91/06/17 91/06/17 91/06/20 91/06/20 91/06/20 91/06/20 91/06/19 91/06/19 91/06/18 91/06/18 91/06/20 91/06/20 91/06/18 91/06/18 91/06/17 91/06/17 91/06/17 91/06/17 91/06/19 91/06/19 91/06/17 91/06/17 91/06/17 91/06/17
The following request against the VIDEOTR2 data source converts the TRANSDATE (HYYMDI) field to alphanumeric format. The alphanumeric representation has format A16 to account for a four-digit year, two-digit month, two-digit day, two slashes between the date components, a space between the date and time, a two-digit hour, a colon between the hour and minute components, and a two-digit minute.
DEFINE FILE VIDEOTR2 DATE/I4 = HPART(TRANSDATE, 'YEAR', 'I4'); ALPHA_DATE/A16 = FPRINT(TRANSDATE,'HYYMDI', ALPHA_DATE); END TABLE FILE VIDEOTR2 PRINT TRANSDATE ALPHA_DATE/R WHERE DATE EQ '1991' ON TABLE SET PAGE NOPAGE END
The output is:
TRANSDATE ALPHA_DATE --------- ---------------- 1991/06/27 02:45 1991/06/27 02:45 1991/06/20 05:15 1991/06/20 05:15 1991/06/21 07:11 1991/06/21 07:11 1991/06/21 01:10 1991/06/21 01:10 1991/06/19 07:18 1991/06/19 07:18 1991/06/19 04:11 1991/06/19 04:11 1991/06/25 01:19 1991/06/25 01:19 1991/06/24 04:43 1991/06/24 04:43 1991/06/24 02:08 1991/06/24 02:08 1991/06/25 01:17 1991/06/25 01:17 1991/06/27 01:17 1991/06/27 01:17 1991/11/17 11:28 1991/11/17 11:28 1991/06/24 10:27 1991/06/24 10:27
If you hold the output in a comma-delimited or other alphanumeric output file, you can see that while the original field propagates only the numeric representation of the value, the converted field propagates the display options as well.
DEFINE FILE VIDEOTR2 DATE/I4 = HPART(TRANSDATE, 'YEAR', 'I4'); ALPHA_DATE/A16 = FPRINT(TRANSDATE,'HYYMDI', ALPHA_DATE); END TABLE FILE VIDEOTR2 PRINT TRANSDATE ALPHA_DATE/R WHERE DATE EQ '1991' ON TABLE HOLD FORMAT COMMA END
The HOLD file follows. The first field represents the original data, and the second field contains the converted values with display options.
"19910627024500000","1991/06/27 02:45" "19910620051500000","1991/06/20 05:15" "19910621071100000","1991/06/21 07:11" "19910621011000000","1991/06/21 01:10" "19910619071800000","1991/06/19 07:18" "19910619041100000","1991/06/19 04:11" "19910625011900000","1991/06/25 01:19" "19910624044300000","1991/06/24 04:43" "19910624020800000","1991/06/24 02:08" "19910625011700000","1991/06/25 01:17" "19910627011700000","1991/06/27 01:17" "19911117112800000","1991/11/17 11:28" "19910624102700000","1991/06/24 10:27"
|
How to: |
The DPART function extracts a specified component from a date field and returns it in numeric format.
DPART(datevalue, 'component', outfield)
where:
Date
Is a full component date.
Alphanumeric
Is the name of the component to be retrieved, enclosed in single quotation marks. Valid values are the following:
For year: YEAR, YY
For month: MONTH, MM
For day: DAY, for day of month: DAY-OF-MONTH.
For quarter: QUARTER, QQ
Integer
Is the field that contains the result, or the integer format of the output value enclosed in single quotation marks.
The following request against the VIDEOTRK data source uses the DPART function to extract the year, month, and day component from the TRANSDATE field.
DEFINE FILE
VIDEOTRK
YEAR/I4 = DPART(TRANSDATE, 'YEAR', 'I4');
MONTH/I4 = DPART(TRANSDATE, 'MM', 'I4');
DAY/I4 = DPART(TRANSDATE, 'DAY', 'I4');
END
TABLE FILE VIDEOTRK
PRINT TRANSDATE YEAR MONTH DAY
BY LASTNAME BY FIRSTNAME
WHERE LASTNAME LT 'DIAZ'
ENDThe output is:
LASTNAME FIRSTNAME TRANSDATE YEAR MONTH DAY
-------- --------- --------- ---- ----- ---
ANDREWS NATALIA 91/06/19 1991 6 19
91/06/18 1991 6 18
BAKER MARIE 91/06/19 1991 6 19
91/06/17 1991 6 17
BERTAL MARCIA 91/06/23 1991 6 23
91/06/18 1991 6 18
CHANG ROBERT 91/06/28 1991 6 28
91/06/27 1991 6 27
91/06/26 1991 6 26
COLE ALLISON 91/06/24 1991 6 24
91/06/23 1991 6 23
CRUZ IVY 91/06/27 1991 6 27
DAVIS JASON 91/06/24 1991 6 24
|
How to: |
|
Reference: |
The DEFECHO parameter sets a default value for the Dialogue Manager &ECHO variable. When &ECHO is set to ON, command lines display on the screen as a procedure executes. When &ECHO is OFF, command lines do not display. The ALL setting displays both FOCUS commands and Dialogue Manager commands as the procedure executes.
In order to protect the privacy of your procedure code, you can use the SET DEFECHO=NONE command or the -SET &ECHO=NONE command to prevent the code from displaying for the entire session or connection.
You can issue the following command in any supported profile or in a FOCEXEC.
SET DEFECHO = {OFF|ON|ALL|NONE}
or
-SET &ECHO = {OFF|ON|ALL|NONE};
where:
Does not display command lines as a procedure executes. OFF is the default value.
Displays FOCUS commands that are expanded and stacked for execution.
Displays Dialogue Manager commands and FOCUS commands that are expanded and stacked for execution.
Prevents procedure code from being displayed (echoed). Once the value of DEFECHO or &ECHO has been set to NONE, it cannot be changed during the session or connection.
The following procedure queries the value of the DEFECHO parameter and issues a TABLE request against the EMPLOYEE data source.
? SET DEFECHO -RUN -TYPE ECHO = &ECHO TABLE FILE EMPLOYEE PRINT CURR_SAL CURR_JOBCODE BY LAST_NAME BY FIRST_NAME END -RUN
The query command output shows that DEFECHO is OFF (the default value).
DEFECHO OFF
The -TYPE command shows that the value of &ECHO is OFF (the default).
ECHO = OFF
Because &ECHO is OFF, the TABLE commands do not display as the procedure executes.
NUMBER OF RECORDS IN TABLE= 12 LINES= 12
PAUSE.. PLEASE ISSUE CARRIAGE RETURN WHEN READY
PAGE 1
LAST_NAME FIRST_NAME CURR_SAL CURR_JOBCODE
--------- ---------- -------- ------------
BANNING JOHN $29,700.00 A17
BLACKWOOD ROSEMARIE $21,780.00 B04
CROSS BARBARA $27,062.00 A17
GREENSPAN MARY $9,000.00 A07
IRVING JOAN $26,862.00 A15
JONES DIANE $18,480.00 B03
MCCOY JOHN $18,480.00 B02
MCKNIGHT ROGER $16,100.00 B02
ROMANS ANTHONY $21,120.00 B04
SMITH MARY $13,200.00 B14
RICHARD $9,500.00 A01
STEVENS ALFRED $11,000.00 A07
END OF REPORT
Now, set DEFECHO=ON and rerun the procedure.
The query command output shows that DEFECHO is ON.
DEFECHO ON
The -TYPE command shows that the value of &ECHO has been changed to ON.
ECHO = ON
Because &ECHO is ON, the TABLE commands display as the procedure executes.
TABLE FILE EMPLOYEE PRINT CURR_SAL CURR_JOBCODE BY LAST_NAME BY FIRST_NAME END
The output displays next.
NUMBER OF RECORDS IN TABLE= 12 LINES= 12
PAUSE.. PLEASE ISSUE CARRIAGE RETURN WHEN READY
PAGE 1
LAST_NAME FIRST_NAME CURR_SAL CURR_JOBCODE
--------- ---------- -------- ------------
BANNING JOHN $29,700.00 A17
BLACKWOOD ROSEMARIE $21,780.00 B04
CROSS BARBARA $27,062.00 A17
GREENSPAN MARY $9,000.00 A07
IRVING JOAN $26,862.00 A15
JONES DIANE $18,480.00 B03
MCCOY JOHN $18,480.00 B02
MCKNIGHT ROGER $16,100.00 B02
ROMANS ANTHONY $21,120.00 B04
SMITH MARY $13,200.00 B14
RICHARD $9,500.00 A01
STEVENS ALFRED $11,000.00 A07
END OF REPORT
Now, issue the SET DEFECHO = NONE command and rerun the procedure.
SET DEFECHO = NONE
The query command output shows that the value of DEFECHO has been changed to NONE.
DEFECHO NONE
The -TYPE command shows that the value of &ECHO is NONE.
ECHO = NONE
Because DEFECHO has the value NONE, the TABLE commands do not display as the procedure executes.
The output is:
NUMBER OF RECORDS IN TABLE= 12 LINES= 12
PAUSE.. PLEASE ISSUE CARRIAGE RETURN WHEN READY
PAGE 1
LAST_NAME FIRST_NAME CURR_SAL CURR_JOBCODE
--------- ---------- -------- ------------
BANNING JOHN $29,700.00 A17
BLACKWOOD ROSEMARIE $21,780.00 B04
CROSS BARBARA $27,062.00 A17
GREENSPAN MARY $9,000.00 A07
IRVING JOAN $26,862.00 A15
JONES DIANE $18,480.00 B03
MCCOY JOHN $18,480.00 B02
MCKNIGHT ROGER $16,100.00 B02
ROMANS ANTHONY $21,120.00 B04
SMITH MARY $13,200.00 B14
RICHARD $9,500.00 A01
STEVENS ALFRED $11,000.00 A07
END OF REPORT
Once the value of DEFECHO has been set to NONE, it cannot be changed. The following SET command attempts to change the value to ON, but the query command output shows that it is still NONE.
SET DEFECHO=ON ? SET DEFECHO DEFECHO NONE
|
How to: |
|
Reference: |
The -READFILE command reads a file by first reading its Master File and creating Dialogue Manager amper variables based on the ACTUAL formats for each field in the Master File. It then reads the file and, if necessary, converts the fields from numeric values to alphanumeric strings before returning them to the created variables. Display options in the USAGE formats are not propagated to the variables. The names of the amper variables are the field names prefixed with an ampersand (&).
-READFILE mastername
where:
Is the name of the Master File to be read.
(FOC339) DIALOGUE MANAGER -READ FAILED: CHECK FILEDEF OR ALLOCATION FOR: -READFILE filename
(FOC702) THE OPTION SPECIFIED IS NOT AVAILABLE WITH TEXT FIELDS: fieldname
The following request creates a binary HOLD file, then uses -READFILE to read the first record from the HOLD file and type the values that were retrieved into Dialogue Manager variables. Note that the names of the variables are the field names prefixed with an ampersand.
TABLE FILE EMPLOYEE PRINT LAST_NAME FIRST_NAME DEPARTMENT CURR_SAL BY EMP_ID ON TABLE HOLD AS READF1 FORMAT BINARY END -RUN -READFILE READF1 -TYPE LAST_NAME IS &LAST_NAME -TYPE FIRST_NAME IS &FIRST_NAME -TYPE DEPARTMENT IS &DEPARTMENT -TYPE CURR_SAL IS &CURR_SAL -TYPE EMP_ID IS &EMP_ID
The output is:
> NUMBER OF RECORDS IN TABLE= 12 LINES= 12
HOLDING BINARY FILE...
LAST_NAME IS STEVENS
FIRST_NAME IS ALFRED
DEPARTMENT IS PRODUCTION
CURR_SAL IS 11000.00
EMP_ID IS 071382660
|
How to: |
Dialogue Manager can return the current date or date and time to a variable in any valid date or date-time format. To return the current date or date-time value to a variable, append the date or date-time format to be returned to &DATE. To suppress trailing blanks, append the format to &DATX.
Note: To remove all punctuation between components, use the variable &YYMD or &DATEHYYMDN. Using the concatenation symbol (|) to remove punctuation between components is not supported.
{&DATE|&DATX}fmtwhere:
Retains trailing blanks in the returned date or date-time value.
Suppresses trailing blanks in the returned date or date-time value.
Is a valid date or date-time format.
The following procedure retrieves the current date in formats MDYY, YYM, MDY, YYQ, and YYMtr (with and without blank suppression). It retrieves the current date and time in format HYYMDS:
-TYPE DATE = &DATE -TYPE MDYY = &DATEMDYY -TYPE YYM = &DATEYYM -TYPE MDY = &DATEMDY -TYPE YYQ = &DATEYYQ -TYPE MBDBYY = &DATEMBDBYY -TYPE YYMtr = &DATEYYMtr with blanks and &DATXYYMtr without blanks -TYPE HYYMDS = &DATEHYYMDS
The output is:
DATE = 05/09/11 MDYY = 05/09/2011 YYM = 2011/05 MDY = 05/09/11 YYQ = 2011 Q2 MBDBYY = 05 09 2011 YYMtr = 2011, May with blanks and 2011, May without blanks HYYMDS = 2011/05/09 14:06:26
|
|
Information Builders |