In this section: |
Starting in FOCUS 7.6.5, you can extract week numbers from date-time values using the ISO 8601 standard definition, call the PATTERN function to see if a string follows a standard pattern, use the REVERSE function to reverse the characters in a string, use the XTPACK function to write packed fields from 1 to 16 bytes to an output file, keep Master Files in memory on a FOCUS Database Server, and use a colon instead of an exclamation point to display an extended currency symbol.
In this section: How to: |
Starting in Version 7.6.5, you can extract week numbers from date-time values using the ISO 8601 standard definition.
The HPART and HNAME subroutines can extract a week number from a date-time value. To determine a week number, they can use ISO 8601 standard week numbering, which defines the first week of the year as the first week in January with four or more days. Any preceding days in January belong to week 52 or 53 of the preceding year. The ISO standard also establishes Monday as the first day of the week.
These functions can also define the first week of the year as the first week in January with seven days. This is the definition they used in prior releases.
You specify which type of week numbering to use by setting the WEEKFIRST parameter.
Since the week number returned by HNAME and HPART functions can be in the current year or the year preceding or following, the week number by itself may not be useful. The function HYYWD returns both the year and the week from a given date-time value.
SET WEEKFIRST = value
where:
Can be:
1 through 7, representing Sunday through Saturday with non-standard week numbering
or
ISO1 through ISO7, representing Sunday through Saturday with ISO standard week numbering. Note: ISO is a synonym for ISO2.
The ISO standard establishes Monday as the first day of the week, so to be fully ISO compliant, the WEEKFIRST parameter should be set to ISO or ISO2.
In the following request, HNAME extracts the week in alphanumeric format from the TRANSDATE field. Changing the WEEKFIRST setting changes the value of the extracted component.
TABLE FILE VIDEOTR2 PRINT CUSTID TRANSDATE AS 'DATE-TIME' AND COMPUTE WEEK_COMPONENT/A10 = HNAME(TRANSDATE, 'WEEK', 'A10'); WHERE DATE EQ 1991 AND QUANTITY GT 1 END
When WEEKFIRST is set to 2, the output is:
CUSTID DATE-TIME WEEK_COMPONENT ------ --------- -------------- 0925 1991/06/27 02:45 25 1297 1991/06/24 04:43 25 1423 1991/06/25 01:17 25
When WEEKFIRST is set to ISO2, the output is:
CUSTID DATE-TIME WEEK_COMPONENT ------ --------- -------------- 0925 1991/06/27 02:45 26 1297 1991/06/24 04:43 26 1423 1991/06/25 01:17 26
The week number returned by HNAME and HPART can actually be in the year preceding or following the input date.
The HYYWD function returns both the year and the week number from a given date-time value.
The output is edited to conform to the ISO standard format for dates with week numbers, yyyy-Www-d.
HYYWD(dtvalue, outfield)
where:
Date-time
Is the date-time value to be edited, the name of a date-time field that contains the value, or an expression that returns the value.
Alphanumeric
Is the field that contains the result, or the format of the output value enclosed in single quotation marks.
The field must be at least 10 characters long. The output is in the following format:
yyyy-Www-d
where:
Is the four-digit year.
Is the two-digit week number (01 to 53).
Is the single-digit day of the week (1 to 7). The d value is relative to the current WEEKFIRST setting. If WEEKFIRST is 2 or ISO2 (Monday), then Monday is represented in the output as 1, Tuesday as 2.
Using the EDIT function, you can extract the individual subfields from this output.
The following request against the VIDEOTR2 data source calls HYYWD to convert the TRANSDATE date-time field to the ISO standard format for dates with week numbers. WEEKFIRST is set to ISO2, which produces ISO standard week numbering:
SET WEEKFIRST = ISO2 TABLE FILE VIDEOTR2 SUM TRANSTOT QUANTITY COMPUTE ISODATE/A10 = HYYWD(TRANSDATE, 'A10'); BY TRANSDATE WHERE QUANTITY GT 1 END
The output is:
TRANSDATE TRANSTOT QUANTITY ISODATE --------- -------- -------- ------- 1991/06/24 04:43 16.00 2 1991-W26-1 1991/06/25 01:17 2.50 2 1991-W26-2 1991/06/27 02:45 16.00 2 1991-W26-4 1996/08/17 05:11 5.18 2 1996-W33-6 1998/02/04 04:11 12.00 2 1998-W06-3 1999/01/30 04:16 13.00 2 1999-W04-6 1999/04/22 06:19 3.75 3 1999-W16-4 1999/05/06 05:14 1.00 2 1999-W18-4 1999/08/09 03:17 15.00 2 1999-W32-1 1999/09/09 09:18 14.00 2 1999-W36-4 1999/10/16 09:11 5.18 2 1999-W41-6 1999/11/05 11:12 2.50 2 1999-W44-5 1999/12/09 09:47 5.18 2 1999-W49-4 1999/12/15 04:04 2.50 2 1999-W50-3
The following request against the VIDEOTR2 data source calls HYYWD to convert the TRANSDATE date-time field to the ISO standard format for dates with week numbers. It then uses the EDIT function to extract the week component from this date. WEEKFIRST is set to ISO2, which produces ISO standard week numbering:
SET WEEKFIRST = ISO2 TABLE FILE VIDEOTR2 SUM TRANSTOT QUANTITY COMPUTE ISODATE/A10 = HYYWD(TRANSDATE, 'A10'); COMPUTE WEEK/A2 = EDIT(ISODATE, '$$$$$$99$$'); BY TRANSDATE WHERE QUANTITY GT 1 AND DATE EQ 1991 END
The output is:
TRANSDATE TRANSTOT QUANTITY ISODATE WEEK --------- -------- -------- ------- ---- 1991/06/24 04:43 16.00 2 1991-W26-1 26 1991/06/25 01:17 2.50 2 1991-W26-2 26 1991/06/27 02:45 16.00 2 1991-W26-4 26
How to: |
Starting in Version 7.6.5, you can examine a string to see if it follows a standard pattern.
The PATTERN function examines a source string and produces a pattern that indicates the sequence of numbers, uppercase letters, and lowercase letters in the input string. In the output pattern, any character from the input that represented a digit becomes the character '9', any character that represents an uppercase letter becomes 'A', and any character that represents a lowercase letter becomes 'a'. Special characters remain unchanged. An unprintable character becomes the character 'X'. This function is useful for examining data to make sure that it follows a standard pattern.
PATTERN (length, infield, outfield)
where:
Alphanumeric
Is a field containing the input string, or a literal string enclosed in single quotation marks.
Numeric
Is the length of infield.
Alphanumeric
Is the name of the field to contain the result or the format of the field enclosed in single quotation marks.
The following 19 records are stored in a fixed format sequential file (with LRECL 14) named TESTFILE:
212-736-6250 212 736 4433 123-45-6789 800-969-INFO 10121-2898 10121 2 Penn Plaza 917-339-6380 917-339-4350 (212) 736-6250 (212) 736-4433 212-736-6250 212-736-6250 212-736-6250 (212) 736 5533 (212) 736 5533 (212) 736 5533 10121 Æ 800-969-INFO
The Master File is:
FILENAME=TESTFILE, SUFFIX=FIX , SEGMENT=TESTFILE, SEGTYPE=S0, $ FIELDNAME=TESTFLD, USAGE=A14, ACTUAL=A14, $
The following request generates a pattern for each instance of TESTFLD and displays them by the pattern that was generated. It shows the count of each pattern and its percentage of the total count. The PRINT command shows which values of TESTFLD generated each pattern.
CMS FILEDEF TESTFILE DISK TESTFILE FOCTEMP A DEFINE FILE TESTFILE PATTERN/A14 = PATTERN (14, TESTFLD, 'A14' ) ; END TABLE FILE TESTFILE SUM CNT.PATTERN AS 'COUNT' PCT.CNT.PATTERN AS 'PERCENT' BY PATTERN PRINT TESTFLD BY PATTERN ON TABLE COLUMN-TOTAL END
Note that the next to last line produced a pattern from an input string that contained an unprintable character, so that character was changed to X. Otherwise, each numeric digit generated a 9 in the output string, each uppercase letter generated the character 'A', and each lowercase letter generated the character 'a'. The output is:
PATTERN COUNT PERCENT TESTFLD ------- ----- ------- ------- (999) 999 9999 3 15.79 (212) 736 5533 (212) 736 5533 (212) 736 5533 (999) 999-9999 2 10.53 (212) 736-6250 (212) 736-4433 9 Aaaa Aaaaa 1 5.26 2 Penn Plaza 999 999 9999 1 5.26 212 736 4433 999-99-9999 1 5.26 123-45-6789 999-999-AAAA 2 10.53 800-969-INFO 800-969-INFO 999-999-9999 6 31.58 212-736-6250 917-339-6380 917-339-4350 212-736-6250 212-736-6250 212-736-6250 99999 1 5.26 10121 99999 X 1 5.26 10121 Æ 99999-9999 1 5.26 10121-2898 TOTAL 19 100.00
How to: |
Starting in Version 7.6.5, you can use REVERSE to reverse the characters in a string.
The REVERSE function reverses the characters of a string. This reversal includes all trailing blanks, which then become leading blanks. However, in an HTML report with SET SHOWBLANKS=OFF (the default value), the leading blanks are not visible.
REVERSE(length, string, outfield)
where:
Integer
Is the length in characters of string and outfield, or a field that contains the length.
Alphanumeric
Is the character string enclosed in single quotation marks, or a field that contains the character string.
Alphanumeric
Is the name of the field that contains the result, or the format of the output value enclosed in single quotation marks.
In the following request against the EMPLOYEE data source, the REVERSE function is used to reverse the characters in the LAST_NAME field to produce the field named REVERSE_LAST. In this field, the trailing blanks from LAST_NAME have become leading blanks. The TRIM function is used to strip the leading blanks from REVERSE_LAST to produce the field named TRIM_REVERSE:
DEFINE FILE EMPLOYEE REVERSE_LAST/A15 = REVERSE(15, LAST_NAME, REVERSE_LAST); TRIM_REVERSE/A15 = TRIM('L', REVERSE_LAST, 15, ' ', 1, 'A15'); END TABLE FILE EMPLOYEE PRINT REVERSE_LAST TRIM_REVERSE BY LAST_NAME END
The output is:
LAST_NAME REVERSE_LAST TRIM_REVERSE --------- ------------ ------------ BANNING GNINNAB GNINNAB BLACKWOOD DOOWKCALB DOOWKCALB CROSS SSORC SSORC GREENSPAN NAPSNEERG NAPSNEERG IRVING GNIVRI GNIVRI JONES SENOJ SENOJ MCCOY YOCCM YOCCM MCKNIGHT THGINKCM THGINKCM ROMANS SNAMOR SNAMOR SMITH HTIMS HTIMS HTIMS HTIMS STEVENS SNEVETS SNEVETS
How to: |
Starting in Version 7.6.5, you can write packed fields from 1 to 16 bytes to an output file.
The XTPACK function stores packed numbers with up to 31 significant digits in an alphanumeric field, retaining decimal data. This permits writing a short or long packed field of any length, 1 to 16 bytes, to an output file.
XTPACK (infield, outlength, outdec, outfield)
where:
Numeric
Is the field that contains the packed value.
Numeric
Is the length of the alphanumeric field that will hold the converted packed field. Can be from 1 to 16.
Numeric
Is the number of decimal positions for outfield.
Alphanumeric
Is the name of the field to contain the result or the format of the field enclosed in single quotation marks.
The following request creates a long packed decimal field named LONGPCK that has format P25.2 and is created by adding the number 11111111111111111111 to PCT_INC. ALPHAPCK (format A13) is the result of applying XTPACK to the long packed field. ALPHAPCK contains a packed number stored in an alphanumeric field and is not printable, so HEX_ALPHAPCK converts it to its printable hexadecimal equivalent using the UFMT subroutine. PCT_INC, LONGPCK, ALPHAPCK, and HEX_ALPHAPCK are then written to an alphanumeric HOLD file named XTOUT.
SET HOLDLIST = PRINTONLY DEFINE FILE EMPLOYEE LONGPCK/P25.2 = PCT_INC + 11111111111111111111; ALPHAPCK/A13 = XTPACK(LONGPCK,13,2,'A13'); END TABLE FILE EMPLOYEE PRINT PCT_INC LONGPCK ALPHAPCK COMPUTE HEX_ALPHAPCK/A26 = UFMT(ALPHAPCK,13,'A26') ; WHERE PCT_INC GT 0 ON TABLE HOLD AS XTOUT FORMAT ALPHA END
The following request prints the PCT_INC, LONGPCK, and HEX_ALPHAPCK fields from the XTOUT HOLD file (ALPHAPCK is not printable):
TABLE FILE XTOUT PRINT PCT_INC LONGPCK HEX_ALPHAPCK END
The output shows the that the HEX_ALPHAPCK field is the packed equivalent of the LONGPCK field. The sign is at the end of packed numbers. Note that a positive sign may be represented as either C or F, depending on operating environment:
PCT_INC LONGPCK HEX_ALPHAPCK ------- ------- ------------ .10 11111111111111111111.10 0001111111111111111111110C .12 11111111111111111111.12 0001111111111111111111112C .10 11111111111111111111.10 0001111111111111111111110C .04 11111111111111111111.04 0001111111111111111111104C .05 11111111111111111111.05 0001111111111111111111105C .10 11111111111111111111.10 0001111111111111111111110C .15 11111111111111111111.15 0001111111111111111111115C .07 11111111111111111111.07 0001111111111111111111107C .04 11111111111111111111.04 0001111111111111111111104C .05 11111111111111111111.05 0001111111111111111111105C
The Master File created as a result of the HOLD command follows. ALPHAPCK is described as A13:
FILENAME=XTOUT , SUFFIX=FIX , $ SEGMENT=XTOUT, SEGTYPE=S0, $ FIELDNAME=PCT_INC, ALIAS=E01, USAGE=F6.2, ACTUAL=A06, $ FIELDNAME=LONGPCK, ALIAS=E02, USAGE=P25.2, ACTUAL=A25, $ FIELDNAME=ALPHAPCK, ALIAS=E03, USAGE=A13, ACTUAL=A13, $ FIELDNAME=HEX_ALPHAPCK, ALIAS=E04, USAGE=A26, ACTUAL=A26, $
You can now create a new data source with the packed values written to the XTOUT HOLD file. In the HOLD file, ALPHAPCK is described as A13, but it contains packed values, not alphanumeric data. In the new data source to be created, those packed values will be loaded into the field named PACKEDVAL that is described as USAGE=P25.2, ACTUAL = P16. The field LONGPCK from the HOLD file contains the alphanumeric equivalent of the packed value, so it is loaded into ALPHAVAL in the new data source:
FILENAME=XTPACKIN, SUFFIX=FIX , $ SEGMENT=XTPACKIN, SEGTYPE=S0, $ FIELDNAME=PCT_INC, ALIAS=E01, USAGE=F6.2, ACTUAL=F4 , $ FIELDNAME=ALPHAVAL, ALIAS=E02, USAGE=A25, ACTUAL=A25 , $ FIELDNAME=PACKEDVAL, ALIAS=E03, USAGE=P25.2, ACTUAL=P16, $
The following request creates the XTPACKIN data source and loads it with data from the HOLD file XTOUT. Note that the incoming packed transaction value from the HOLD file is described as P13:
CMS FILEDEF XTPACKIN DISK XTPACKIN FOCTEMP A CREATE FILE XTPACKIN -RUN MODIFY FILE XTPACKIN FIXFORM PCT_INC/6 ALPHAVAL/25 PACKEDVAL/P13 DATA ON XTOUT END
The following request prints the values in the data source just created:
TABLE FILE XTPACKIN PRINT * END
The output shows that the long packed data was loaded and displays correctly. In the data source, the LONGPCK field contains the alphanumeric value and the ALPHAPCK field contains the packed equivalent:
PCT_INC ALPHAVAL PACKEDVAL ------- -------- --------- .10 11111111111111111111.10 11111111111111111111.10 .12 11111111111111111111.12 11111111111111111111.12 .10 11111111111111111111.10 11111111111111111111.10 .04 11111111111111111111.04 11111111111111111111.04 .05 11111111111111111111.05 11111111111111111111.05 .10 11111111111111111111.10 11111111111111111111.10 .15 11111111111111111111.15 11111111111111111111.15 .07 11111111111111111111.07 11111111111111111111.07 .04 11111111111111111111.04 11111111111111111111.04 .05 11111111111111111111.05 11111111111111111111.05
Reference: |
Starting in Version 7.6.5, the extended currency symbol format has been extended. The colon (:) has been added as an alternate for the exclamation point (!) for displaying extended currency symbols.
Extended currency symbol formats are specified as two-character combinations in the last position of any numeric display format. The first character in the combination can be either an exclamation point (!) or a colon (:). The exclamation point is not consistent on all EBCDIC code pages and may produce unexpected behavior if the code page you are using translates the exclamation point differently:
Display Option |
Description |
Example |
---|---|---|
!d or :d |
Fixed dollar sign. |
D12.2:d |
!D or :D |
Floating dollar sign. |
D12.2:D |
!e or :e |
Fixed euro symbol. |
F9.2:e |
!E or :E |
Floating euro symbol on the left side. |
F9.2:E |
!F or :F |
Floating euro symbol on the right side. |
F9.2:F |
!l or :l |
Fixed British pound sign. |
D12.1:l |
!L or :L |
Floating British pound sign. |
D12.1:L |
!y or :y |
Fixed Japanese yen symbol. |
I9:y |
!Y or :Y |
Floating Japanese yen symbol. |
I9:Y |
The following guidelines apply:
How to: Reference: |
Starting in Version 7.6.5, keep Master Files in memory on a sink machine.
The SET SUWEDGE command provides a mechanism for keeping Master Files in memory on a FOCUS Database Server (sink machine) even though no users are accessing the files. This enhances performance by eliminating repeated parsing of Master Files on FOCUS Database Servers that have a large number of users running multiple applications using different files on the server.
You can specify that a number of files be wedged open, that specific named files be wedged open, or a combination of both. The maximum number of files that will be wedged open is 128, regardless of how many you specify.
Place one or more of the following commands in the FOCUS Database Server profile (PROFILE HLI on z/VM, and member HLIPROF of the FOCEXEC data set on z/OS):
SET SUWEDGE = {n|ddname} [, ddname ...]
where:
Is a number of Master Files to be wedged open. You can specify any number, but once 128 files are wedged open, additional files will not be wedged. The default is zero.
Is the ddname of a specific file to be wedged open in addition to the n files specified by number (if any).
Note: To set both a number of files to be wedged and one or more specific DDNAMEs to be wedged, you can issue the SET SUWEDGE command multiple times or specify multiple options in one SET SUWEDGE command. Specific DDNAMEs are not counted in the number of files specified (as long as the total does not exceed the maximum number of wedged files allowed). If you specify a number multiple times, the last one specified is the one used.
The following commands wedge open any three FOCUS data sources plus the EMPLOYEE and CAR data sources:
SET SUWEDGE = 3 SET SUWEDGE = EMPLOYEE SET SUWEDGE = CAR
or
SET SUWEDGE = 3 SET SUWEDGE = EMPLOYEE, CAR
or
SET SUWEDGE = EMPLOYEE, CAR SET SUWEDGE = 3
or
SET SUWEDGE = 3, EMPLOYEE, CAR
Information Builders |