DB_LOOKUP(look_mf, srcfld1, lookfld1, srcfld2, lookfld2, ..., returnfld);

where:

look_mf

Is the lookup Master File.

srcfld1, srcfld2 ...

Are fields from the source file used to locate a matching record in the lookup file.

lookfld1, lookfld2 ...

Are columns from the lookup file that share values with the source fields. Only columns in the table or file can be used; columns created with DEFINE cannot be used. For multi-segment synonyms only columns in the top segment can be used.

returnfld

Is the name of a column in the lookup file whose value is returned from the matching lookup record. Only columns in the table or file can be used; columns created with DEFINE cannot be used.

• The maximum number of pairs that can be used to match records is 63.
• If the lookup file is a fixed format sequential file, it must be sorted and retrieved in the same order as the source file. The sequential file's key field must be the first lookup field specified in the DB_LOOKUP request. If it is not, no records will match.

  In addition, if a DB_LOOKUP request against a sequential file is issued in a DEFINE FILE command, you must clear the DEFINE FILE command at the end of the TABLE request that references it or the lookup file will remain open. It will not be reusable until closed and may cause problems when you exit FOCUS. Other types of lookup files can be reused without clearing the DEFINE. They will be cleared automatically when all DEFINE fields are cleared.

• If the lookup field has the MISSING=ON attribute in its Master File and the DEFINE or COMPUTE command specifies MISSING ON, the missing value is returned when the lookup field is missing. Without MISSING ON in both places, the missing value is converted to a default value (blank for an alphanumeric field, zero for a numeric field).
• Source records display on the report output even if they lack a matching record in the lookup file.
• Only real fields in the lookup Master File are valid as lookup and return fields.
• If there are multiple rows in the lookup table where the source field is equal to the lookup field, the first value of the return field is returned.

The following procedure creates a fixed format sequential file named GSALE from the GGSALES data source. The fields in this file are PRODUCT (product description), CATEGORY (product category), and PCD (product code). The file is sorted on the PCD field:

SET ASNAMES = ON
TABLE FILE GGSALES
SUM PRODUCT CATEGORY
BY PCD
ON TABLE HOLD AS GSALE FORMAT ALPHA
END

The following Master File is generated as a result of the HOLD command:

FILENAME=GSALE, SUFFIX=FIX     , $
  SEGMENT=GSALE, SEGTYPE=S1, $
    FIELDNAME=PCD, ALIAS=E01, USAGE=A04, ACTUAL=A04, $
    FIELDNAME=PRODUCT, ALIAS=E02, USAGE=A16, ACTUAL=A16, $
    FIELDNAME=CATEGORY, ALIAS=E03, USAGE=A11, ACTUAL=A11, $

The following TABLE request against the GGPRODS data source, sorts the report on the field that matches the key field in the lookup file. It retrieves the value of the CATEGORY field from the GSALE lookup file by matching on the product code and product description fields. Note that the DEFINE FILE command is cleared at the end of the request:

DEFINE FILE GGPRODS
PCAT/A11 MISSING ON = DB_LOOKUP(GSALE,  PRODUCT_ID, PCD,
         PRODUCT_DESCRIPTION, PRODUCT, CATEGORY);
END
TABLE FILE GGPRODS
PRINT PRODUCT_DESCRIPTION PCAT
BY PRODUCT_ID
END
DEFINE FILE GGPRODS CLEAR
END

Because the GSALE Master File does not define the CATEGORY field with the MISSING=ON attribute, the PCAT column displays a blank in those rows that have no matching record in the lookup file:

Product
Code     Product           PCAT
-------  -------           ----
B141     Hazelnut
B142     French Roast
B144     Kona
F101     Scone             Food
F102     Biscotti          Food
F103     Croissant         Food
G100     Mug               Gifts
G104     Thermos           Gifts
G110     Coffee Grinder    Gifts
G121     Coffee Pot        Gifts

If you add the MISSING=ON attribute to the CATEGORY field in the GSALE Master File, the PCAT column displays a missing data symbol in rows that do not have a matching record in the lookup file:

Product
Code     Product           PCAT
-------  -------           ----
B141     Hazelnut          .
B142     French Roast      .
B144     Kona              .
F101     Scone             Food
F102     Biscotti          Food
F103     Croissant         Food
G100     Mug               Gifts
G104     Thermos           Gifts
G110     Coffee Grinder    Gifts
G121     Coffee Pot        Gifts

HEXTR(source, 'componentstring', length, outfield)

where:

source

Is the a date-time value from which to extract the specified components.

componentstring

Is a string of codes, in any order, that indicates which components are to be extracted and moved to the output date-time field. The following table shows the valid values. The string is considered to be terminated by any character not in this list:

Code	Description
C	century (the two high-order digits only of the four-digit year)
Y	year (the two low-order digits only of the four-digit year)
YY	Four digit year.
M	month
D	day
H	hour
I	minutes
S	seconds
s	milliseconds (the three high-order digits of the six-digit microseconds value)
u	microseconds (the three low-order digits of the six-digit microseconds value)
m	All six digits of the microseconds value.

length

Is the length of the returned date-time value. Valid values are:

8 - indicates a time value that includes milliseconds.

10 - indicates a time value the includes microseconds.

outfield

Is the field that contains the result, or the format of the output value enclosed in single quotation marks. This field must be in date-time format (data type H).

The VIDEOTR2 data source has a date-time field named TRANSDATE of type HYYMDI. The following request selects all records containing the time 09:18AM, regardless of the value of the remaining components:

TABLE FILE VIDEOTR2
PRINT TRANSDATE
BY LASTNAME
BY FIRSTNAME
WHERE HEXTR(TRANSDATE, 'HI', 8, 'HYYMDI') EQ DT(09:18AM)
END

The output is:

LASTNAME         FIRSTNAME   TRANSDATE
--------         ---------   ---------
DIZON            JANET       1999/11/05 09:18
PETERSON         GLEN        1999/09/09 09:18

HMASK(source, 'componentstring', input, length, outfield)

where:

source

Is the date-time value from which the specified components are extracted.

componentstring

Is a string of codes, in any order, that indicates which components are to be extracted and moved to the output date-time field. The following table shows the valid values. The string is considered to be terminated by any character not in this list:

Code	Description
C	century (the two high-order digits only of the four-digit year)
Y	year (the two low-order digits only of the four-digit year)
YY	Four digit year.
M	month
D	day
H	hour
I	minutes
S	seconds
s	milliseconds (the three high-order digits of the six-digit microseconds value)
u	microseconds (the three low-order digits of the six-digit microseconds value)
m	All six digits of the microseconds value.

input

Is the date-time value that provides all the components for the output that are not specified in the component string.

length

Is the length of the returned date-time value. Valid values are:

8 - indicates a time value that includes milliseconds.

10 - indicates a time value the includes microseconds.

outfield

Is the field that contains the result, or the format of the output value enclosed in single quotation marks. This field must be in date-time format (data type H).

HMASK processing is subject to the DTSTRICT setting. Moving the day (D) component without the month (M) component could lead to an invalid result, which is not permitted if the DTSTRICT setting is ON. Invalid date-time values cause any date-time function to return zeroes.

The VIDEOTRK data source has a date-time field named TRANSDATE of format HYYMDI. The following request changes any TRANSDATE value with a time component greater than 11:00 to 8:30 of the following day. First the HEXTR function extracts the hour and minutes portion of the value and compares it to 11:00. If it is greater than 11:00, the HADD function calls HMASK to change the time to 08:30 and adds one day to the date:

DEFINE FILE VIDEOTR2
ORIG_TRANSDATE/HYYMDI = TRANSDATE;
TRANSDATE =
IF HEXTR(TRANSDATE, 'HI', 8, 'HHI') GT DT(12:00)
   THEN HADD (HMASK(DT(08:30), 'HISs', TRANSDATE, 8, 'HYYMDI'), 'DAY',
    1,8, 'HYYMDI')
   ELSE TRANSDATE;
END
 
TABLE FILE VIDEOTR2
PRINT ORIG_TRANSDATE TRANSDATE
BY LASTNAME
BY FIRSTNAME
WHERE ORIG_TRANSDATE NE TRANSDATE
END

The output is:

LASTNAME         FIRSTNAME   ORIG_TRANSDATE    TRANSDATE       
--------         ---------   --------------    ---------       
BERTAL           MARCIA      1999/07/29 12:19  1999/07/30 08:30
GARCIA           JOANN       1998/05/08 12:48  1998/05/09 08:30
                             1999/11/30 12:12  1999/12/01 08:30
PARKER           GLENDA      1999/01/06 12:22  1999/01/07 08:30
RATHER           MICHAEL     1998/02/28 12:33  1998/03/01 08:30
WILSON           KELLY       1999/06/26 12:34  1999/06/27 08:30

Issue the following command in any supported profile, or in a FOCEXEC, or at the command prompt:

SET DMPRECISION = {OFF|n}

where:

OFF

Specifies truncation without rounding after the decimal point. OFF is the default value.

n

Is a positive number from 0-9, indicating the point of rounding. Note that n=0 results in a rounded integer value.

The following table below shows the result of dividing 20 by 3 with varying DMPRECISION (DMP) settings:

• When using SET DMPRECISION, you must include -RUN after the SET DMPRECISION command to ensure that it is set prior to any numeric -SET commands.
• As the actual conversion to double precision follows the rules for the operating system, the values may vary from platform to platform.

Issue the following command in any supported profile, or in a focexec, or at the command prompt, to display settable parameter values by functional area:

? SET BY CATEGORY

The functional areas available for display are listed below.

To view the current values for parameters in all categories, enter:

? SET BY CATEGORY

This displays the existing parameter values in each category (first four of twenty shown).

                        MEMORY UTILIZATION PARAMETERS
 
 BINS                 64    CACHE                 0    CALCMEMORY            5
 LOADLIMIT            64    MDIBINS            8000    POOLMEMORY        16384
 XFBINS     16 (passive)
 
                           DATE CONTROL PARAMETERS
 
 ALLOWCVTERR         OFF    BUSDAYS         _MTWTF_    DATEDISPLAY         OFF
 DATEFNS              ON    DATEFORMAT          MDY    DATETIME  STARTUP/RESET
 DEFCENT              19    DTSTANDARD          OFF    DTSTRICT             ON
 HDAY                       TESTDATE          TODAY    WEEKFIRST             7
 YRTHRESH              0
 
                       SECURITY ENVIRONMENT PARAMETERS
 
 PASS             ??????    PERMPASS         ??????    SUSI                OFF
 
                         POOLTABLE RELATED PARAMETERS
 DEJAVU             ????    ESTLINES              0    ESTRECORDS            0
 HRATIO             ????    MAXADRTABLE        ????    MAXEXTSRTS         ????
 MAXMNM             ????    MAXMRGSTRNGS       ????    MAXPOOLMEM        32768
 MAXSORTS           ????    MINADRTABLE        ????    MINEXTVSPACE       ????
 MINMTI             ????    MINMTX             ????    MTXFDG             ????
 MXMFOC             ????    POOL                OFF    POOLBATCH           OFF
 POOLFEATURE         OFF    POOLMEMORY        16384    POOLORDER          ????
 POOLRESERVE        1024    PTBDBG             ????    PTDFCORE           ????
 ROUNDR             ????    SURPRI             ????    THRSHF             ????
 THRSHX             ????    TRUST1             ????

Issue the following commands in any supported profile, in a focexec, or at the command prompt, to display settable parameter values for a specified functional area:

? SET CATEGORY categoryname

where:

categoryname

Is one of the categories.

Issue the following command in any supported profile, in a focexec, or at the command prompt, to display settable parameter values for a specified functional area:

? SET CATEGORY HELP

SET CDN = option

ON TABLE SET CDN option

where:

option

Can be one of the following:

OFF

Turns CDN off. For example, the number 3,045,000.76 is represented as 3,045,000.76. OFF should be used for the USA, Canada, Mexico, and the United Kingdom. OFF is the default (standard) value.

ON

Designates the decimal separator as a comma and the thousands separator as a period. For example, the number 3,045,000.76 is represented as 3.045.000,76. ON should be used for Germany, Denmark, Italy, Spain, and Brazil.

SPACE

Sets the decimal point as a comma, and the thousands separator as a space. For example, the number 3,045,000.76 is represented as 3 045 000,76. SPACE should be used for France, Norway, Sweden, and Finland.

QUOTE

Sets the decimal point as a comma and the thousands separator as an apostrophe. For example, the number 3,045,000.76 is represented as 3'045'000,76. QUOTE should be used for Switzerland.

QUOTEP

Sets the decimal point as a period and the thousands separator as an apostrophe. For example, the number 3,045,000.76 is represented as 3'045'000.76.

If the display format of a report is Excel 2000 or later, Continental Decimal Notation is controlled by the settings on the user's computer. That is, numbers in report output are formatted according to the convention of the locale (location) set in regional or browser language options.

SET CDN=QUOTEP
TABLE FILE EMPLOYEE
PRINT FIRST_NAME LAST_NAME SALARY
END

The output is:

FIRST_NAME  LAST_NAME                 SALARY
----------  ---------                 ------
ALFRED      STEVENS               $11'000.00
ALFRED      STEVENS               $10'000.00
MARY        SMITH                 $13'200.00
DIANE       JONES                 $18'480.00
DIANE       JONES                 $17'750.00
RICHARD     SMITH                  $9'500.00
RICHARD     SMITH                  $9'050.00
JOHN        BANNING               $29'700.00
JOAN        IRVING                $26'862.00
JOAN        IRVING                $24'420.00
ANTHONY     ROMANS                $21'120.00
JOHN        MCCOY                 $18'480.00
ROSEMARIE   BLACKWOOD             $21'780.00
ROGER       MCKNIGHT              $16'100.00
ROGER       MCKNIGHT              $15'000.00
MARY        GREENSPAN              $9'000.00
MARY        GREENSPAN              $8'650.00

SET DEFECHO = {OFF|ON|ALL}

where:

OFF

Establishes OFF as the default value for &ECHO. OFF is the default value.

ON

Establishes ON as the default value for &ECHO. ON displays FOCUS commands that are expanded and stacked for execution.

ALL

Establishes ALL as the default value for &ECHO. ALL displays Dialogue Manager commands and FOCUS commands that are expanded and stacked for execution.

The following procedure executes two FOCEXECs. The first FOCEXEC, RJUST1, right justifies the last name of employees in the EMPLOYEE data source, and the second FOCEXEC, CTRFLD1, centers the names. The first EX command sets &ECHO to ON:

EX RJUST1 ECHO=ON
EX CTRFLD1

The RJUST1 procedure is echoed before the report output is generated. The CTRFLD1 procedure uses the default value, OFF, for &ECHO, so the procedure is not echoed.

Echo for RJUST1:

TABLE FILE EMPLOYEE
PRINT LAST_NAME AND COMPUTE
RIGHT_NAME/A15 = RJUST(15, LAST_NAME, RIGHT_NAME);
WHERE DEPARTMENT EQ 'MIS';
END

Report output for RJUST1:

LAST_NAME        RIGHT_NAME
---------        ----------
SMITH                      SMITH
JONES                      JONES
MCCOY                      MCCOY
BLACKWOOD              BLACKWOOD
GREENSPAN              GREENSPAN
CROSS                      CROSS

Report output for CTRFLD1:

LAST_NAME        CENTER_NAME
---------        -----------
SMITH               SMITH
JONES               JONES
MCCOY               MCCOY
BLACKWOOD         BLACKWOOD
GREENSPAN         GREENSPAN
CROSS               CROSS

Running the procedure with SET DEFECHO=ON, but specifying ECHO=OFF on the EX command for RJUST1, causes the CTRFLD1 procedure to use the default of ON. However, the RJUST1 procedure uses the specified value of OFF:

SET DEFECHO = ON
EX RJUST1 ECHO=OFF
EX CTRFLD1

Report output for RJUST1:

LAST_NAME        RIGHT_NAME
---------        ----------
SMITH                      SMITH
JONES                      JONES
MCCOY                      MCCOY
BLACKWOOD              BLACKWOOD
GREENSPAN              GREENSPAN
CROSS                      CROSS

Echo for CTRFLD1:

TABLE FILE EMPLOYEE
PRINT LAST_NAME AND COMPUTE
CENTER_NAME/A12 = CTRFLD(LAST_NAME, 12, 'A12');
WHERE DEPARTMENT EQ 'MIS'
END

Report output for CTRFLD1:

LAST_NAME        CENTER_NAME
---------        -----------
SMITH               SMITH
JONES               JONES
MCCOY               MCCOY
BLACKWOOD         BLACKWOOD
GREENSPAN         GREENSPAN
CROSS               CROSS

? SITECODE

Assume you installed the License Management facility with site code A52709b.

Issue the following query command :

? SITECODE

The output is:

SITE CODE A527O9b

If the site code is not installed, the ? SITECODE query returns the following message:

SITE CODE NOT AVAILABLE

SET PERMPASS=userpass

where:

userpass

Is the user password used for all access to data sources with DBA security rules established in their associated Master Files.

Consider the MOVIES Master File with the following DBA rules in effect:

DBA=USER1,$
USER = USERR,  ACCESS = R ,$
USER = USERU,  ACCESS = U ,$
USER = USERW,  ACCESS = W ,$
USER = USERRW, ACCESS = RW,$

The following FOCEXEC sets a permanent password:

SET PERMPASS = USERU
TABLE FILE MOVIES 
PRINT TITLE BY DIRECTOR
END

The user has ACCESS=U and, therefore, is not allowed to issue a table request against the file:

(FOC047) THE USER DOES NOT HAVE SUFFICIENT ACCESS RIGHTS TO THE FILE:
CAR
BYPASSING TO END OF COMMAND

The permanent password cannot be changed:

SET PERMPASS = USERRW

permanent PASS is in effect. Your PASS will not be honored.
VALUE WAS NOT CHANGED

The user password cannot be changed:

SET PASS = USERRW

permanent PASS is in effect. Your PASS will not be honored.
VALUE WAS NOT CHANGED

If you use FOCUSID to set passwords externally, these external passwords may be overridable or non-overridable. If they are non-overridable, they take precedence over the PERMPASS setting.

SET PERMPASS is supported in MSO. The profiles available in order of precedence are:

• Member PROFILE in the data set allocated to ddname MSOPROF. This profile is executed for all users.
• Members with users' user IDs as names in the data set allocated to ddname MSOPROF. The profile is executed for the user corresponding to the user ID.
• Member PROFILE in the data set allocated by a user to ddname FOCEXEC. This profile is executed for that specific user and is a standard FOCUS profile.
• Member SHELPROF in the data set allocated to ddname FOCEXEC in the MSO startup JCL. This profile is executed for all users and is a standard FOCUS profile.

&var.QUOTEDSTRING

where:

&var

Is a Dialogue Manager variable.

The following example shows the results of the QUOTEDSTRING suffix on input strings.

-SET &A = ABC;
-SET &B = 'ABC';
-SET &C = O'BRIEN;
-SET &D = 'O'BRIEN';
-SET &E = 'O''BRIEN';
-SET &F = O''BRIEN;
-SET &G = OBRIEN';
-TYPE  ORIGINAL = &A QUOTED = &A.QUOTEDSTRING
-TYPE  ORIGINAL = &B QUOTED = &B.QUOTEDSTRING
-TYPE  ORIGINAL = &C QUOTED = &C.QUOTEDSTRING
-TYPE  ORIGINAL = &D QUOTED = &D.QUOTEDSTRING
-TYPE  ORIGINAL = &E QUOTED = &E.QUOTEDSTRING
-TYPE  ORIGINAL = &F QUOTED = &F.QUOTEDSTRING
-TYPE  ORIGINAL = &G QUOTED = &G.QUOTEDSTRING

The output is:

ORIGINAL = ABC        QUOTED = 'ABC'
ORIGINAL = ABC        QUOTED = 'ABC'
ORIGINAL = O'BRIEN    QUOTED = 'O''BRIEN'
ORIGINAL = O'BRIEN    QUOTED = 'O''BRIEN'
ORIGINAL = O'BRIEN    QUOTED = 'O''BRIEN'
ORIGINAL = O''BRIEN   QUOTED = 'O''''BRIEN'
ORIGINAL = OBRIEN'    QUOTED = 'OBRIEN'''

Note: The -SET command will remove single quotes around a string. Notice in the example above that the result of -SET &B = 'ABC' was changed to ORIGINAL = ABC (as shown in the output), prior to the QUOTEDSTRING conversion.

The following -TYPE command accepts quoted or unquoted input and displays quoted output.

-TYPE THE QUOTED VALUE IS: &E.QUOTEDSTRING

The output is:

PLEASE SUPPLY VALUES REQUESTED
 
E=
O'BRIEN
THE QUOTED VALUE IS: 'O''BRIEN'

The following procedure creates an Oracle table named SQLVID from the VIDEOTRK data source.

TABLE FILE VIDEOTRK
SUM CUSTID EXPDATE PHONE STREET CITY STATE ZIP
   TRANSDATE PRODCODE TRANSCODE QUANTITY TRANSTOT
BY LASTNAME BY FIRSTNAME
WHERE LASTNAME NE 'NON-MEMBER'
ON TABLE HOLD
END
-RUN
CREATE FILE SQLVID
-RUN
MODIFY FILE SQLVID
FIXFORM FROM HOLD
DATA ON HOLD
END

Consider the following SQL Translator request:

SET TRACEUSER = ON
SET TRACEON = STMTRACE//CLIENT
SQL
SELECT *
FROM SQLVID WHERE LASTNAME  = &1.QUOTEDSTRING;
END

When this request is executed, you must enter a last name, in this case O'BRIEN:

PLEASE SUPPLY VALUES REQUESTED
 
1=
 
O'BRIEN

In the generated SQL request, the character string used for the comparison is correctly enclosed in single quotation marks, and the embedded single quote is doubled:

SELECT SQLCOR01.CIN , SQLCOR01.LN , SQLCOR01.FN ,
SQLCOR01.EXDAT , SQLCOR01.TEL , SQLCOR01.STR , SQLCOR01.CITY ,
SQLCOR01.PROV , SQLCOR01.POSTAL_CODE , SQLCOR01.OUTDATE ,
SQLCOR01.PCOD , SQLCOR01.TCOD , SQLCOR01.NO , SQLCOR01.TTOT
FROM SQLVID SQLCOR01  WHERE SQLCOR01.LN  = 'O''BRIEN';

The output is:

CIN   LN               FN     ...
---   --               --     ...
5564  O'BRIEN          DONALD ...

The following input variations are translated to the correct form of quoted string demonstrated in the trace.

'O'BRIEN'
'O''BRIEN'

Any other variation results in:

• A valid string that does not match the database value and does not return any rows. For example, O''''BRIEN becomes 'O''''''''BRIEN' in the WHERE predicate.
• An invalid string that produces one of the following messages:

  Error - Semi-colon or END expected

  Error - Missing or Misplaced quotes

  Error - (value entered) is not a valid column

  Error - Syntax error on line ... Unbalanced quotes

Strings without embedded single quotation marks can be entered without quotes or embedded in single quotation marks, either SMITH or 'SMITH'.

If you use &1 without the QUOTEDSTRING suffix in the request, acceptable input strings that retrieve O'Brien's record are:

'''O'''BRIEN'''
'''O''''BRIEN'''

Using &1 without the QUOTEDSTRING suffix, the acceptable form of a string without embedded single quotation marks is '''SMITH'''.

To make a string enclosed in single quotation marks acceptable without the QUOTEDSTRING suffix, use '&1' in the request. In this case, in order to retrieve O'Brien's record, you must enter the string that would have resulted from the QUOTEDSTRING suffix:

'O''''BRIEN'

To enter a string without embedded single quotation marks using '&1', you can either omit the surrounding single quotation marks or include them: SMITH or 'SMITH'.

Note: The form '&1.QUOTEDSTRING' is not supported.

• An unmatched single quotation mark at the beginning of a character string is treated as invalid input and generates the following message:

  (FOC257) MISSING QUOTE MARKS:  value;

In a DEFINE FILE command, use the following syntax

DEFINE 
field/{TXn|An} = {alphaexpression|textfield};
END

In a TABLE COMPUTE or MODIFY COMPUTE command, use the following syntax

COMPUTE field/{TXn|An} = {alphaexpression|textfield};

where:

field

Is a text or alphanumeric field that will receive the text field value or result of the alphanumeric expression.

n

Is the length of the alphanumeric field or the output display length of the text field that will receive the value from the right hand side of the COMPUTE or DEFINE command.

alphaexpression

Is the name of an alphanumeric field, an alphanumeric literal, or an alphanumeric expression.

textfield

Is the name of a text field.

This example uses the COURSES data source, which contains a text field, to create an alphanumeric field named ADESC, which truncates the text field at 36 characters, and a new text field named NEWDESC, which is a text version of ADESC:

DEFINE FILE COURSES
ADESC/A36   = DESCRIPTION;
NEWDESC/TX36 = ADESC;
END
 
TABLE FILE COURSES
PRINT ADESC NEWDESC
END

The output is:

ADESC                                 NEWDESC
-----                                 -------
This course provides the DP professi  This course provides the DP professi
Anyone responsible for designing FOC  Anyone responsible for designing FOC
This is a course in FOCUS efficienci  This is a course in FOCUS efficienci

COMPUTE commands in Maintain do not support text fields.

DATETRAN (indate, '(intype)', '([formatops])', 'lang', outlen, output)

where:

indate

Is the input date (in date format) to be formatted. Note that the date format cannot be an alphanumeric or numeric format with date display options (legacy date format).

intype

Is one of the following character strings indicating the input date components and the order in which you want them to display, enclosed in parentheses and single quotation marks:

Single Component Input Type	Description
'(W)'	Day of week component only (original format must have only W component).
'(M)'	Month component only (original format must have only M component).

Two-Component Input Type	Description
'(YYM)'	Four-digit year followed by month.
'(YM)'	Two-digit year followed by month.
'(MYY)'	Month component followed by four-digit year.
'(MY)'	Month component followed by two-digit year.

Three- Component Input Type	Description
'(YYMD)'	Four-digit year followed by month followed by day.
'(YMD)'	Two-digit year followed by month followed by day.
'(DMYY)'	Day component followed by month followed by four-digit year.
'(DMY)'	Day component followed by month followed by two-digit year.
'(MDYY)'	Month component followed by day followed by four-digit year.
'(MDY)'	Month component followed by day followed by two-digit year.
'(MD)'	Month component followed by day (derived from three-component date by ignoring year component).
'(DM)'	Day component followed by month (derived from three-component date by ignoring year component).

formatops

Is a string of zero or more formatting options enclosed in parentheses and single quotation marks. The parentheses and quotation marks are required even if you do not specify formatting options. Formatting options fall into the following categories:

• Options for suppressing initial zeros in month or day numbers.
• Options for translating month or day components to full or abbreviated uppercase or default case (mixed case or lowercase depending on the language) names.
• Date delimiter options and options for punctuating a date with commas.

Valid options for suppressing initial zeros in month or day numbers are:

Format Option	Description
m	Zero-suppresses months (displays numeric months before October as 1 through 9 rather than 01 through 09).
d	Displays days before the tenth of the month as 1 through 9 rather than 01 through 09.
dp	Displays days before the tenth of the month as 1 through 9 rather than 01 through 09 with a period after the number.
do	Displays days before the tenth of the month as 1 through 9. For English (langcode EN) only, displays an ordinal suffix (st, nd, rd, or th) after the number.

Valid month and day name translation options are:

Format Option	Description
T	Displays month as an abbreviated name with no punctuation, all uppercase.
TR	Displays month as a full name, all uppercase.
Tp	Displays month as an abbreviated name followed by a period, all uppercase.
t	Displays month as an abbreviated name with no punctuation. The name is all lowercase or initial uppercase, depending on language code.
tr	Displays month as a full name. The name is all lowercase or initial uppercase, depending on language code.
tp	Displays month as an abbreviated name followed by a period. The name displays in the default case of the specified language (for example, all lowercase for French and Spanish, initial uppercase for English and German).
W	Includes an abbreviated day of the week name at the start of the displayed date, all uppercase with no punctuation.
WR	Includes a full day of the week name at the start of the displayed date, all uppercase.
Wp	Includes an abbreviated day of the week name at the start of the displayed date, all uppercase, followed by a period.
w	Includes an abbreviated day of the week name at the start of the displayed date with no punctuation. The name displays in the default case of the specified language (for example, all lowercase for French and Spanish, initial uppercase for English and German).
wr	Includes a full day of the week name at the start of the displayed date. The name displays in the default case of the specified language (for example, all lowercase for French and Spanish, initial uppercase for English and German).
wp	Includes an abbreviated day of the week name at the start of the displayed date followed by a period. The name displays in the default case of the specified language (for example, all lowercase for French and Spanish, initial uppercase for English and German).
X	Includes an abbreviated day of the week name at the end of the displayed date, all uppercase with no punctuation.
XR	Includes a full day of the week name at the end of the displayed date, all uppercase.
Xp	Includes an abbreviated day of the week name at the end of the displayed date, all uppercase, followed by a period.
x	Includes an abbreviated day of the week name at the end of the displayed date with no punctuation. The name displays in the default case of the specified language (for example, all lowercase for French and Spanish, initial uppercase for English and German).
xr	Includes a full day of the week name at the end of the displayed date. The name displays in the default case of the specified language (for example, all lowercase for French and Spanish, initial uppercase for English and German).
xp	Includes an abbreviated day of the week name at the end of the displayed date followed by a period. The name displays in the default case of the specified language (for example, all lowercase for French and Spanish, initial uppercase for English and German).

Valid date delimiter options are:

Format Option	Description
B	Uses a blank as the component delimiter. This is the default if the month or day of week is translated or if comma is used.
.	Uses a period as the component delimiter.
-	Uses a minus sign as the component delimiter. This is the default when the conditions for a blank default delimiter are not satisfied.
/	Uses a slash as the component delimiter.
|	Omits component delimiters.
K	Uses appropriate Asian characters as component delimiters.
c	Places a comma after the month name (following T, Tp, TR, t, tp, or tr). Places a comma and blank after the day name (following W, Wp, WR, w, wp, or wr). Places a comma and blank before the day name (following X, XR, x, or xr).
e	Displays the Spanish or Portuguese word de or DE between the day and month and between the month and year. The case of the word de is determined by the case of the month name. If the month is displayed in uppercase, DE is displayed; otherwise de is displayed. Useful for formats DMY, DMYY, MY, and MYY.
D	Inserts a comma after the day number and before the general delimiter character specified.
Y	Inserts a comma after the year and before the general delimiter character specified.

lang

Is the two-character standard ISO code for the language into which the date should be translated, enclosed in single quotation marks. Valid language codes are:

'DE'	German
'DU'	Dutch
'EN'	English
'ES'	Spanish
'FR'	French
'GR'	Greek
'HE'	Hebrew
'JA'	Japanese
'NO'	Norwegian (bokmål)
'PT'	Portuguese
'SV'	Swedish
'ZH'	Simplified Chinese

outlen

Numeric

Is the length of the output field in bytes. If the length is insufficient, an all blank result is returned. If the length is greater than required, the field is padded with blanks on the right.

output

Alphanumeric

Is the name of the field that contains the translated date, or its format enclosed in single quotation marks.

• The output field, though it must be type A and not AnV, may in fact contain variable length information, since the lengths of month names and day names can vary, and also month and day numbers may be either one or two bytes long if a zero-suppression option is chosen. Unused bytes are filled with blanks.
• All invalid and inconsistent inputs result in all blank output strings. Missing data also results in blank output.
• The base dates (1900-12-31 and 1900-12 or 1901-01) are treated as though the DATEDISPLAY setting were ON (that is, not automatically shown as blanks). To suppress the printing of base dates, which have an internal integer value of 0, test for 0 before calling DATETRAN. For example:

  RESULT/A40 = IF DATE EQ 0 THEN ' ' ELSE
                  DATETRAN (DATE, '(YYMD)', '(.t)', 'FR', 40, 'A40');

• Valid translated date components are contained in files named DTLNGlng where lng is a three-character code that specifies the language. These files must be accessible for each language into which you want to translate dates.
• If you use a terminal emulator program, it must be set to use a code page that can display the accent marks and characters in the translated dates. You may not be able to display dates translated into European and Asian characters at the same time. Similarly, if you want to print the translated dates, your printer must be capable of printing the required characters.
• The DATETRAN function is not supported in Dialogue Manager.

The following request prints the day of the week in the default case of the specific language:

DEFINE FILE VIDEOTRK
TRANS1/YYMD=20050104;
TRANS2/YYMD=20051003;
 
DATEW/W=TRANS1     ;
DATEW2/W=TRANS2    ;
DATEYYMD/YYMDW=TRANS1    ;
DATEYYMD2/YYMDW=TRANS2   ;
 
OUT1A/A8=DATETRAN(DATEW, '(W)', '(wr)', 'EN', 8 , 'A8') ;
OUT1B/A8=DATETRAN(DATEW2, '(W)', '(wr)', 'EN', 8 , 'A8') ;
OUT1C/A8=DATETRAN(DATEW, '(W)', '(wr)', 'ES', 8 , 'A8') ;
OUT1D/A8=DATETRAN(DATEW2, '(W)', '(wr)', 'ES', 8 , 'A8') ;
OUT1E/A8=DATETRAN(DATEW, '(W)', '(wr)', 'FR', 8 , 'A8') ;
OUT1F/A8=DATETRAN(DATEW2, '(W)', '(wr)', 'FR', 8 , 'A8') ;
OUT1G/A8=DATETRAN(DATEW, '(W)', '(wr)', 'DE', 8 , 'A8') ;
OUT1H/A8=DATETRAN(DATEW2, '(W)', '(wr)', 'DE', 8 , 'A8') ;
END
 
TABLE FILE VIDEOTRK
HEADING
"FORMAT wr"
""
"Full day of week name at beginning of date, default case (wr)"
"English / Spanish / French / German"
""
SUM OUT1A AS '' OUT1B AS '' TRANSDATE NOPRINT
OVER OUT1C AS '' OUT1D AS ''
OVER OUT1E AS '' OUT1F AS ''
OVER OUT1G AS '' OUT1H AS ''
ON TABLE HOLD FORMAT HTML
ON TABLE SET PAGE-NUM OFF
ON TABLE SET STYLE *
GRID=OFF, $
END

The output is:

The following request prints a blank delimited date with an abbreviated month name in English. Initial zeros in the day number are suppressed, and a suffix is added to the end of the number:

DEFINE FILE VIDEOTRK
TRANS1/YYMD=20050104;
TRANS2/YYMD=20050302;
 
DATEW/W=TRANS1     ;
DATEW2/W=TRANS2    ;
DATEYYMD/YYMDW=TRANS1    ;
DATEYYMD2/YYMDW=TRANS2   ;
 
OUT2A/A15=DATETRAN(DATEYYMD,  '(MDYY)', '(Btdo)', 'EN', 15, 'A15') ;
OUT2B/A15=DATETRAN(DATEYYMD2, '(MDYY)', '(Btdo)', 'EN', 15, 'A15') ;
END
TABLE FILE VIDEOTRK
HEADING
"FORMAT Btdo"
""
"Blank-delimited (B)"
"Abbreviated month name, default case (t)"
"Zero-suppress day number, end with suffix (do)"
"English"
""
SUM OUT2A AS '' OUT2B AS '' TRANSDATE NOPRINT
ON TABLE HOLD FORMAT HTML
ON TABLE SET PAGE-NUM OFF
END

The output is:

The following request prints a blank delimited date with an abbreviated month name in German. Initial zeros in the day number are suppressed, and a period is added to the end of the number:

DEFINE FILE VIDEOTRK
TRANS1/YYMD=20050104;
TRANS2/YYMD=20050302;
 
DATEW/W=TRANS1     ;
DATEW2/W=TRANS2    ;
DATEYYMD/YYMDW=TRANS1    ;
DATEYYMD2/YYMDW=TRANS2   ;
 
OUT3A/A12=DATETRAN(DATEYYMD,  '(DMYY)', '(Btdp)', 'DE', 12, 'A12');
OUT3B/A12=DATETRAN(DATEYYMD2, '(DMYY)', '(Btdp)', 'DE', 12, 'A12');
END
TABLE FILE VIDEOTRK
HEADING
"FORMAT Btdp"
""
"Blank-delimited (B)"
"Abbreviated month name, default case (t)"
"Zero-suppress day number, end with period (dp)"
"German"
""
SUM OUT3A AS '' OUT3B AS '' TRANSDATE NOPRINT
ON TABLE HOLD FORMAT HTML
ON TABLE SET PAGE-NUM OFF
END

The output is:

The following request prints a blank delimited date in French with a full day name at the beginning and a full month name, in lower case (the default for French):

DEFINE FILE VIDEOTRK
TRANS1/YYMD=20050104;
TRANS2/YYMD=20050302;
 
DATEW/W=TRANS1     ;
DATEW2/W=TRANS2    ;
DATEYYMD/YYMDW=TRANS1    ;
DATEYYMD2/YYMDW=TRANS2   ;
 
OUT4A/A30 = DATETRAN(DATEYYMD,  '(DMYY)', '(Bwrtr)', 'FR', 30, 'A30');
OUT4B/A30 = DATETRAN(DATEYYMD2, '(DMYY)', '(Bwrtr)', 'FR', 30, 'A30');
END
 
TABLE FILE VIDEOTRK
HEADING
"FORMAT Bwrtr"
""
"Blank-delimited (B)"
"Full day of week name at beginning of date, default case (wr)"
"Full month name, default case (tr)"
"English"
""
SUM OUT4A AS '' OUT4B AS '' TRANSDATE NOPRINT
ON TABLE HOLD FORMAT HTML
ON TABLE SET PAGE-NUM OFF
END

The output is:

The following request prints a blank delimited date in Spanish with a full day name at the beginning in lowercase (the default for Spanish) followed by a comma, and with the word de between the day number and month and between the month and year:

DEFINE FILE VIDEOTRK
TRANS1/YYMD=20050104;
TRANS2/YYMD=20050302;
 
DATEW/W=TRANS1     ;
DATEW2/W=TRANS2    ;
DATEYYMD/YYMDW=TRANS1    ;
DATEYYMD2/YYMDW=TRANS2   ;
 
OUT5A/A30=DATETRAN(DATEYYMD,  '(DMYY)', '(Bwrctrde)', 'ES', 30, 'A30');
OUT5B/A30=DATETRAN(DATEYYMD2, '(DMYY)', '(Bwrctrde)', 'ES', 30, 'A30');
END
 
TABLE FILE VIDEOTRK
HEADING
"FORMAT Bwrctrde"
""
"Blank-delimited (B)"
"Full day of week name at beginning of date, default case (wr)"
"Comma after day name (c)"
"Full month name, default case (tr)"
"Zero-suppress day number (d)"
"de between day and month and between month and year (e)"
"Spanish"
""
SUM OUT5A AS '' OUT5B AS '' TRANSDATE NOPRINT
ON TABLE HOLD FORMAT HTML
ON TABLE SET PAGE-NUM OFF
END

The output is:

The following request prints a date in Japanese characters with a full month name at the beginning, in the default case and with zero suppression:

DEFINE FILE VIDEOTRK
TRANS1/YYMD=20050104;
TRANS2/YYMD=20050302;
 
DATEW/W=TRANS1     ;
DATEW2/W=TRANS2    ;
DATEYYMD/YYMDW=TRANS1    ;
DATEYYMD2/YYMDW=TRANS2   ;
 
OUT6A/A30=DATETRAN(DATEYYMD , '(YYMD)', '(Ktrd)', 'JA', 30, 'A30');
OUT6B/A30=DATETRAN(DATEYYMD2, '(YYMD)', '(Ktrd)', 'JA', 30, 'A30');
END
 
TABLE FILE VIDEOTRK
HEADING
"FORMAT Ktrd"
""
"Japanese characters (K in conjunction with the language code JA)"
"Full month name at beginning of date, default case (tr)"
"Zero-suppress day number (d)"
"Japanese"
""
SUM OUT6A AS '' OUT6B AS '' TRANSDATE NOPRINT
ON TABLE HOLD FORMAT HTML
ON TABLE SET PAGE-NUM OFF
END

The output is:

The following request prints a blank delimited date in Greek with a full day name at the beginning in the default case followed by a comma, and with a full month name in the default case:

DEFINE FILE VIDEOTRK
TRANS1/YYMD=20050104;
TRANS2/YYMD=20050302;
 
DATEW/W=TRANS1     ;
DATEW2/W=TRANS2    ;
DATEYYMD/YYMDW=TRANS1    ;
DATEYYMD2/YYMDW=TRANS2   ;
 
OUT7A/A30=DATETRAN(DATEYYMD , '(DMYY)', '(Bwrctr)', 'GR', 30, 'A30');
OUT7B/A30=DATETRAN(DATEYYMD2, '(DMYY)', '(Bwrctr)', 'GR', 30, 'A30');
END
TABLE FILE VIDEOTRK
HEADING
"FORMAT Bwrctrde"
""
"Blank-delimited (B)"
"Full day of week name at beginning of date, default case (wr)"
"Comma after day name (c)"
"Full month name, default case (tr)"
"Greek"
""
SUM OUT7A AS '' OUT7B AS '' TRANSDATE NOPRINT
ON TABLE HOLD FORMAT HTML
ON TABLE SET PAGE-NUM OFF
END

The output is:

SET DTSTANDARD = {OFF|ON|STANDARD|STANDARDU}

where:

OFF

Does not provide compatibility with the ISO 8601:2000 date-time notation standard. OFF is the default value.

ON|STANDARD

Enables recognition and output of the ISO standard formats, including use of T as the delimiter between date and time, use of period or comma as the delimiter of fractional seconds, use of Z at the end of "universal" times, and acceptance of inputs with time zone information. STANDARD is a synonym for ON.

STANDARDU

Enables ISO standard formats (like STANDARD) and also, where possible, converts input strings to the equivalent "universal" time (formerly known as "Greenwich Mean Time"), thus enabling applications to store all date-time values in a consistent way.

HtimeZ

where:

time

Is a format option for the time components. For example HHI specifies a time component consisting of a two-digit hour and a two-digit minute.

Z

Indicates universal time. Z is incompatible with AM/PM output. It prints Z at the end of the output string to indicate a universal time.

With the STANDARD and STANDARDU settings, the separator for dates is always a hyphen.

The separator between date and time is blank by default. However, if you specify the following separator option, the date and time are separated by the character T:

HdateUtime[Z]

where:

date

Is a format option for the date components. For example HYYMD specifies a date component consisting of a four-digit year, a two-digit month, and a two-digit day.

U

Is the date separator option that displays the character T between the date and time. U is incompatible with AM/PM output.

time

Is a format option for the time components. For example, I specifies a time component consisting of a two-digit hour and a two-digit minute.

Z

Indicates universal time.

With DTSTANDARD settings of STANDARD and STANDARDU, the following time formats can be read as input:

14:30[:20,99]

14:30[:20.99]Z

15:30[:20,99]+01
15:30[:20,99]+0100
15:30[:20,99]+01:00

09:30[:20.99]-05

The following component names and abbreviations are supported when calling a date-time function. Note that the component names and abbreviations can be entered in uppercase or lowercase:

The following request finds the number of days between the ADD_MONTH and TRANSDATE fields. The month component in the call to the HADD function is abbreviated as mm. The day component in the call to the HDIFF function is abbreviated as DD.

Standard universal date output is specified with the Z option in the ADD_MONTH format. The U separator option displays the date and time separated by the character T:

SET DTSTANDARD = STANDARD
TABLE FILE VIDEOTR2
PRINT CUSTID TRANSDATE AS 'DATE-TIME' AND COMPUTE
ADD_MONTH/HYYMDUSZ = HADD(TRANSDATE, 'mm', 2, 8, 'HYYMDUSZ');
DIFF_DAYS/D12.2 = HDIFF(ADD_MONTH, TRANSDATE, 'DD', 'D12.2');
WHERE DATE EQ 2000
END

The output is:

CUSTID  DATE-TIME         ADD_MONTH                  DIFF_DAYS
------  ---------         ---------                  ---------
1118    2000/06/26 05:45  2000-08-26T05:45:00Z          61.00
1237    2000/02/05 03:30  2000-04-05T03:30:00Z          60.00

TABLE FILE ...
{PRINT|SUM} field ... 
COMPUTE rrate/fmt = MIRR(cashflow, finrate, reinvrate, output);
WITHIN {sort_field|TABLE}

where:

field ...

Are fields that appear in the report output.

rrate

Is the field that contains the calculated return rate.

fmt

Is the format of the return rate. The data type must be D.

cashflow

Is a numeric field. Each value represents either a payment (negative value) or income (positive value) for one period. The values must be in the correct sequence in order for for the sequence of cash flows to be calculated correctly. The dates corresponding to each cash flow should be equally spaced and sorted in chronological order. The calculation requires at least one negative value and one positive value in the cashflow field. If the values are all positive or all negative, a zero result is returned.

finrate

Is a finance rate for negative cash flows. This value must be expressed as a non-negative decimal fraction between 0 and 1. It must be constant within each sort group for which a return rate is calculated, but it can change between sort groups.

reinvrate

Is the reinvestment rate for positive cash flows. This value must be expressed as a non-negative decimal fraction between 0 and 1. It must be constant within each sort group but can change between sort groups. It must be constant within each sort group for which a return rate is calculated, but it can change between sort groups.

output

Is the name of the field that contains the return rate, or its format enclosed in single quotation marks.

sort_field

Is a field that sorts the report output and groups it into subsets of rows on which the function can be calculated separately. To calculate the function using every row of the report output, use the WITHIN TABLE phrase. A WITHIN phrase is required.

• This function is only supported in a COMPUTE command with the WITHIN phrase.
• The cash flow field must contain at least one negative value and one positive value.
• Dates must be equally spaced.
• Missing cash flows or dates are not supported.

The following request calculates modified internal return rates for categories of products. It assumes a finance charge of ten percent and a reinvestment rate of ten percent. The request is sorted by date so that the correct cash flows are calculated. The rate returned by the function is multiplied by 100 in order to express it as a percent rather than a decimal value. Note that the format includes the % character. This causes a percent symbol to display, but it does not calculate a percent.

In order to create one cash flow value per date, the values are summed. NEWDOLL is defined in order to create negative values in each category as required by the function:

DEFINE FILE GGSALES
 SDATE/YYM = DATE;
 SYEAR/Y = SDATE;
 NEWDOLL/D12.2 = IF DATE LT '19970401' THEN -1 * DOLLARS ELSE DOLLARS;
END
 
TABLE FILE GGSALES
  SUM NEWDOLL
  COMPUTE RRATE/D7.2% = MIRR(NEWDOLL, .1, .1, RRATE) * 100;
  WITHIN CATEGORY
  BY CATEGORY
  BY SDATE
  WHERE SYEAR EQ 97
END

A separate rate is calculated for each category because of the WITHIN CATEGORY phrase. A portion of the output is shown:

Category     SDATE           NEWDOLL      RRATE
--------     -----           -------      -----
Coffee       1997/01     -801,123.00     15.11%
             1997/02     -682,340.00     15.11%
             1997/03     -765,078.00     15.11%
             1997/04      691,274.00     15.11%
             1997/05      720,444.00     15.11%
             1997/06      742,457.00     15.11%
             1997/07      747,253.00     15.11%
             1997/08      655,896.00     15.11%
             1997/09      730,317.00     15.11%
             1997/10      724,412.00     15.11%
             1997/11      620,264.00     15.11%
             1997/12      762,328.00     15.11%
Food         1997/01     -672,727.00     16.24%
             1997/02     -699,073.00     16.24%
             1997/03     -642,802.00     16.24%
             1997/04      718,514.00     16.24%
             1997/05      660,740.00     16.24%
             1997/06      734,705.00     16.24%
             1997/07      760,586.00     16.24%

To calculate one modified internal return rate for all of the report data, use the WITHIN TABLE phrase. In this case, the data does not have to be sorted by CATEGORY:

DEFINE FILE GGSALES
 SDATE/YYM = DATE;
 SYEAR/Y = SDATE;
 NEWDOLL/D12.2 = IF DATE LT '19970401' THEN -1 * DOLLARS ELSE DOLLARS;
END
 
TABLE FILE GGSALES
  SUM NEWDOLL
  COMPUTE RRATE/D7.2% = MIRR(NEWDOLL, .1, .1, RRATE) * 100;
  WITHIN TABLE
  BY SDATE
  WHERE SYEAR EQ 97
END

The output is:

SDATE           NEWDOLL      RRATE
-----           -------      -----
1997/01   -1,864,129.00     15.92%
1997/02   -1,861,639.00     15.92%
1997/03   -1,874,439.00     15.92%
1997/04    1,829,838.00     15.92%
1997/05    1,899,494.00     15.92%
1997/06    1,932,630.00     15.92%
1997/07    2,005,402.00     15.92%
1997/08    1,838,863.00     15.92%
1997/09    1,893,944.00     15.92%
1997/10    1,933,705.00     15.92%
1997/11    1,865,982.00     15.92%
1997/12    2,053,923.00     15.92%

TABLE FILE ...
{PRINT|SUM} field ...  
COMPUTE rrate/fmt = XIRR (cashflow, dates,guess, maxiterations, output);
WITHIN {sort_field|TABLE}

where:

field ...

Are fields that appear in the report output.

rrate

Is the field that contains the calculated return rate.

fmt

Is the format of the return rate. The data type must be D.

cashflow

Is a numeric field. Each value of this field represents either a payment (negative value) or income (positive value) for one period. The values must be in the correct sequence in order for the sequence of cash flows to be calculated correctly. The dates corresponding to each cash flow should be equally spaced and sorted in chronological order. The calculation requires at least one negative value and one positive value in the cashflow field. If the values are all positive or all negative, a zero result is returned.

dates

Is a date field containing the cash flow dates. The dates must be full component dates with year, month, and day components. Dates cannot be stored in fields with format A, I, or P. They must be stored in date fields (for example, format YMD, not AYMD). There must be the same number of dates as there are cash flow values. The number of dates must be the same as the number of cash flows.

guess

Is an (optional) initial estimate of the expected return rate expressed as a decimal. The default value is .1 (10%). To accept the default, supply the value 0 (zero) for this argument.

maxiterations

Is an (optional) number specifying the maximum number of iterations that can be used to resolve the rate using Newton's method. 50 is the default value. To accept the default, supply the value 0 (zero) for this argument. The rate is considered to be resolved when successive iterations do not differ by more than 0.0000003. If this level of accuracy is achieved within the maximum number of iterations, calculation stops at that point. If it is not achieved after reaching the maximum number of iterations, calculation stops and the value calculated by the last iteration is returned.

output

D

Is the name of the field that contains the return rate, or its format enclosed in single quotation marks.

sort_field

Is a field that sorts the report output and groups it into subsets of rows on which the function can be calculated separately. To calculate the function using every row of the report output, use the WITHIN TABLE phrase. A WITHIN phrase is required.

• This function is only supported in a COMPUTE command with the WITHIN phrase.
• The cash flow field must contain at least one negative value and one positive value.
• Dates cannot be stored in fields with format A, I, or P. They must be stored in date fields (for example, format YMD, not AYMD).
• Cash flows or dates with missing values are not supported.

The following request creates a FOCUS data source with cash flows and dates and calculates the internal return rate.

The Master File for the data source is:

FILENAME=XIRR01,SUFFIX=FOC
SEGNAME=SEG1,SEGTYPE=S1
FIELDNAME=DUMMY,FORMAT=A2,$
FIELDNAME=DATES,FORMAT=YYMD,$
FIELDNAME=CASHFL,FORMAT=D12.4,$
END

The procedure to create the data source is:

CREATE FILE XIRR01
MODIFY FILE XIRR01
FREEFORM DUMMY DATES CASHFL
DATA
AA,19980101,-10000. ,$
BB,19980301,2750.   ,$
CC,19981030,4250.   ,$
DD,19990215,3250.   ,$
EE,19990401,2750.   ,$
END

The request is sorted by date so that the correct cash flows can be calculated. The rate returned by the function is multiplied by 100 in order to express it as a percent rather than a decimal value. Note that the format includes the % character. This causes a percent symbol to display, but it does not calculate a percent:

TABLE FILE XIRR01
PRINT CASHFL
COMPUTE RATEX/D12.2%=XIRR(CASHFL, DATES, 0., 0., RATEX) * 100;
WITHIN TABLE
BY DATES
END

One rate is calculated for the entire report because of the WITHIN TABLE phrase:

DATES               CASHFL            RATEX
-----               ------            -----
1998/01/01    -10,000.0000           37.49%
1998/03/01      2,750.0000           37.49%
1998/10/30      4,250.0000           37.49%
1999/02/15      3,250.0000           37.49%
1999/04/01      2,750.0000           37.49%

PTOA(number, '(format)', outfield)

where:

number

Numeric P (packed decimal)

Is the number to be converted, or the name of the field that contains the number.

format

Alphanumeric

Is the output format of the number enclosed in both single quotation marks and parentheses. Only packed decimal format is supported. Include any edit options that you want to display in the output.

The format value does not have to have the same length or number of decimal places as the original field. If you change the number of decimal places, the result is rounded. If you make the length too short to hold the integer portion of the number, asterisks display instead of the number.

If you use a field name for this argument, specify the name without quotation marks or parentheses. However, parentheses must be included around the format stored in this field. For example:

FMT/A10 = '(P12.2C)';

You can then use this field as the format argument when using the function in your request:

COMPUTE ALPHA_GROSS/A20 = PTOA(PGROSS, FMT, ALPHA_GROSS);

outfield

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 of this argument must be greater than the length of number and must account for edit options and a possible negative sign.

In Dialogue Manager, you must specify the format. In Maintain, you must specify the name of the field.

PTOA is called twice to convert the PGROSS field from packed decimal to alphanumeric format. The format specified in the first call to the function is stored in a virtual field named FMT. The format specified in the second call to the function does not include decimal places, so the value is rounded when displayed:

DEFINE FILE EMPLOYEE
PGROSS/P18.2=GROSS;
FMT/A10='(P14.2C)';
END
TABLE FILE EMPLOYEE PRINT PGROSS NOPRINT
COMPUTE AGROSS/A17 = PTOA(PGROSS, FMT, AGROSS); AS ''
COMPUTE BGROSS/A37 = '<- THIS AMOUNT IS' |
                     PTOA(PGROSS, '(P5C)', 'A6') |
                     ' WHEN ROUNDED'; AS '' IN +1
BY HIGHEST 1 PAY_DATE NOPRINT
BY LAST_NAME NOPRINT
END

The output is:

2,475.00 <- THIS AMOUNT IS 2,475 WHEN ROUNDED
1,815.00 <- THIS AMOUNT IS 1,815 WHEN ROUNDED
2,255.00 <- THIS AMOUNT IS 2,255 WHEN ROUNDED
  750.00 <- THIS AMOUNT IS   750 WHEN ROUNDED
2,238.50 <- THIS AMOUNT IS 2,239 WHEN ROUNDED
1,540.00 <- THIS AMOUNT IS 1,540 WHEN ROUNDED
1,540.00 <- THIS AMOUNT IS 1,540 WHEN ROUNDED
1,342.00 <- THIS AMOUNT IS 1,342 WHEN ROUNDED
1,760.00 <- THIS AMOUNT IS 1,760 WHEN ROUNDED
1,100.00 <- THIS AMOUNT IS 1,100 WHEN ROUNDED
  791.67 <- THIS AMOUNT IS   792 WHEN ROUNDED
  916.67 <- THIS AMOUNT IS   917 WHEN ROUNDED

PUTDDREC(ddname, dd_len, record_string, record_len, outfield)

where:

ddname

Alphanumeric

Is the logical name assigned to the sequential file in a CMS FILEDEF command. For information about the CMS FILEDEF command, see the Overview and Operating Environments manual.

dd_len

Numeric

Is the number of characters in the logical name.

record_string

Alphanumeric

Is the character string to be added as the new record in the sequential file.

record_len

Numeric

Is the number of characters to add as the new record. It cannot be larger than the number of characters in record_string. To write all of record_string to the file, record_len should equal the number of characters in record_string and should not exceed the record length declared in the CMS FILEDEF command. If record_len is shorter than the length declared in the CMS FILEDEF command, the resulting file may contain extraneous characters at the end of each record. If record_string is longer than the length declared in the CMS FILEDEF command, record_string may be truncated in the resulting file.

outfield

Integer

Is the return code, which can have one of the following values:

Return Code	Description
0	Record is added.
-1	FILEDEF statement is not found.
-2	Error while opening the file.
-3	Error while adding the record to the file.

CLSDDREC(outfield)

where:

outfield

Integer

Is the return code, which can be one of the following values:

Return Code	Description
0	Files are closed.
-1	Error while closing the files.

• The open, close, and write operations are handled by the operating system. Therefore, the requirements for writing to the file and the results of deviating from the instructions when calling PUTDDREC are specific to your operating environment. Make sure you are familiar with and follow the guidelines for your operating system when performing input/output operations.
• You can call PUTDDREC in a DEFINE FILE command or in a DEFINE in the Master File. However, PUTDDREC does not open the file until its field name is referenced in a request.

The following example defines a new file whose logical name is PUTDD1. The TABLE request then calls PUTDDREC for each employee in the EMPLOYEE data source and writes a record to the file composed of the employee's last name, first name, employee ID, current job code, and current salary (converted to alphanumeric using the EDIT function). The return code of zero (in OUT1) indicates that the calls to PUTDDREC were successful:

CMS FILEDEF PUTDD1 DISK PUTDD1 DATA A 
-RUN
TABLE FILE EMPLOYEE
PRINT EMP_ID CURR_JOBCODE AS 'JOB' CURR_SAL
COMPUTE SALA/A12 = EDIT(CURR_SAL); NOPRINT
COMPUTE EMP1/A50= LAST_NAME|FIRST_NAME|EMP_ID|CURR_JOBCODE|SALA;
NOPRINT
COMPUTE OUT1/I1 = PUTDDREC('PUTDD1',6, EMP1, 50, OUT1);
BY LAST_NAME BY FIRST_NAME
END

The output is:

LAST_NAME        FIRST_NAME  EMP_ID     JOB         CURR_SAL  OUT1
---------        ----------  ------     ---         --------  ----
BANNING          JOHN        119329144  A17       $29,700.00     0
BLACKWOOD        ROSEMARIE   326179357  B04       $21,780.00     0
CROSS            BARBARA     818692173  A17       $27,062.00     0
GREENSPAN        MARY        543729165  A07        $9,000.00     0
IRVING           JOAN        123764317  A15       $26,862.00     0
JONES            DIANE       117593129  B03       $18,480.00     0
MCCOY            JOHN        219984371  B02       $18,480.00     0
MCKNIGHT         ROGER       451123478  B02       $16,100.00     0
ROMANS           ANTHONY     126724188  B04       $21,120.00     0
SMITH            MARY        112847612  B14       $13,200.00     0
                 RICHARD     119265415  A01        $9,500.00     0
STEVENS          ALFRED      071382660  A07       $11,000.00     0

After running this request, the sequential file contains the following records:

BANNING        JOHN      119329144A17000000029700
BLACKWOOD      ROSEMARIE 326179357B04000000021780
CROSS          BARBARA   818692173A17000000027062
GREENSPAN      MARY      543729165A07000000009000
IRVING         JOAN      123764317A15000000026862
JONES          DIANE     117593129B03000000018480
MCCOY          JOHN      219984371B02000000018480
MCKNIGHT       ROGER     451123478B02000000016100
ROMANS         ANTHONY   126724188B04000000021120
SMITH          MARY      112847612B14000000013200
SMITH          RICHARD   119265415A01000000009500
STEVENS        ALFRED    071382660A07000000011000

The following example defines a new file whose logical name is PUTDD1. The first -SET command creates a record to add to this file. The second -SET command calls PUTDDREC to add the record. The last -SET command calls CLSDDREC to close the file. The return codes are displayed to make sure operations were successful:

CMS FILEDEF PUTDD1 DISK PUTDD1 DATA A 
-RUN
-SET &EMP1 = 'SMITH'|'MARY'|'A07'|'27000';
-TYPE DATA = &EMP1
-SET &OUT1 = PUTDDREC('PUTDD1',6, &EMP1, 17, 'I1');
-TYPE PUT RESULT = &OUT1
-SET &OUT1 = CLSDDREC('I1');
-TYPE CLOSE RESULT = &OUT1

The output is:

DATA = SMITHMARYA0727000
PUT RESULT = 0
CLOSE RESULT = 0

After running this procedure, the sequential file contains the following record:

SMITHMARYA0727000

To override message severity levels:

1. Create a message severity override file. The file must be a sequential file sorted in order of message number. The format of each line is described in How to Override Error or Warning Messages.
2. Identify the message severity override file by including the following command in a profile or FOCEXEC.

   SET ERROROUT=[OVERRIDE/]filename

   where:

   filename

   Is the name of the override file. On z/OS, filename must be a member in the concatenation of data sets allocated to DDNAME ERRORS. On z/VM, filename is the name of the override file. The override file must have filetype ERRORS.

To implement an override file, use the following syntax. Note that you cannot set this parameter with an ON TABLE SET command.

SET ERROROUT=[OVERRIDE/]filename

where:

filename

Is the name of the file containing override entries. OVERRIDE/filename is a synonym for filename. The file must be sorted in ascending order of message number. Each entry has the following form:

message_number {Warning|W|Error|E}

or

start_message_number - end_message_number {Warning|W|Error|E}

where:

message_number is the number of a message whose severity level you want to redefine.

Warning or W defines the message as a warning message. The option is not case-sensitive.

Error or E defines the message as an error message. The option is not case-sensitive.

start_message_number is the beginning of a range of message numbers that will be assigned the same severity level.

end_message_number is the end of the range of message numbers.

Any combination of single messages and message ranges may be included in the override file. Each item must be separated from the next item by a space.

To revert to default error-condition handling after using a message severity override file, issue the following command:

SET ERROROUT = {ON|OFF}

where:

ON

Reinstates the default message file.

The application terminates if a condition is an error, and can continue if a condition is a warning.

OFF

Reinstates the default message file. OFF is the default value.

With ERROROUT=OFF, the application is permitted to continue to the next control point within the application regardless of the severity level of the error posted.

Consider the following procedure that generates two warning messages (FOC095 and FOC096) about the SKIP-LINE and SUBFOOT commands:

TABLE FILE EMPLOYEE
HEADING CENTER
"Departmental Salary Report </1"
PRINT CURR_JOBCODE AS 'Job Code'
BY DEPARTMENT AS 'Department'
BY LAST_NAME AS 'Last Name'
  ON LAST_NAME SKIP-LINE
BY CURR_SAL AS 'Current,Salary'
ON CURR_SAL SUBFOOT
  "<13 *** WARNING: <LAST_NAME 's salary exceeds recommended guidelines."
  WHEN CURR_SAL GT 27000;
ON DEPARTMENT SKIP-LINE
ON DEPARTMENT SUBFOOT
-*"<13 Total salary expense for the <DEP dept is: <ST.CURR_SAL"
END

With ERROROUT=OFF, the warning messages display and then the report is generated:

 ERROR AT OR NEAR LINE     14  IN PROCEDURE OVERRIDEFOCEXEC *
(FOC095) WARNING. PREVIOUS USE OF THIS OPTION IS OVERRIDDEN: SKIP-LINE
 ERROR AT OR NEAR LINE     17  IN PROCEDURE OVERRIDEFOCEXEC *
(FOC096) WARNING. NO TEXT SUPPLIED BELOW SUBHEAD OR SUBFOOT
 ERROR AT OR NEAR LINE     17  IN PROCEDURE OVERRIDEFOCEXEC *
(FOC096) WARNING. NO TEXT SUPPLIED BELOW SUBHEAD OR SUBFOOT
 NUMBER OF RECORDS IN TABLE=       12  LINES=     12
 
 PAUSE.. PLEASE ISSUE CARRIAGE RETURN WHEN READY

             Departmental Salary Report
 
                                     Current
Department  Last Name                Salary   Job Code
----------  ---------                -------  --------
 
MIS         BLACKWOOD             $21,780.00  B04
            CROSS                 $27,062.00  A17
            *** WARNING: CROSS 's salary exceeds recommended guidelines.
            GREENSPAN              $9,000.00  A07
            JONES                 $18,480.00  B03
            MCCOY                 $18,480.00  B02
            SMITH                 $13,200.00  B14
 
PRODUCTION  BANNING               $29,700.00  A17
           *** WARNING: BANNING 's salary exceeds recommended guidelines.
            IRVING                $26,862.00  A15
            MCKNIGHT              $16,100.00  B02
            ROMANS                $21,120.00  B04
            SMITH                  $9,500.00  A01
            STEVENS               $11,000.00  A07

The ERRORS file named NEWERR identifies 095 and 096 as errors rather than warnings:

095 E
096 E

Running the same request with the following setting causes the application to terminate without displaying the report output:

SET ERROROUT = NEWERR

The following messages display with this setting in effect:

 ERROR AT OR NEAR LINE     14  IN PROCEDURE OVERRIDEFOCEXEC *
(FOC095) WARNING. PREVIOUS USE OF THIS OPTION IS OVERRIDDEN: SKIP-LINE
Exiting due to Exit on Error...
 ERROR AT OR NEAR LINE     17  IN PROCEDURE OVERRIDEFOCEXEC *
(FOC096) WARNING. NO TEXT SUPPLIED BELOW SUBHEAD OR SUBFOOT
Exiting due to Exit on Error...
 ERROR AT OR NEAR LINE     17  IN PROCEDURE OVERRIDEFOCEXEC *
(FOC096) WARNING. NO TEXT SUPPLIED BELOW SUBHEAD OR SUBFOOT
Exiting due to Exit on Error...
 ...RETRIEVAL KILLED
(FOC026) THE REPORT IS NO LONGER AVAILABLE
Exiting due to Exit on Error...

For additional details about error handling, see your documentation for SET ERROROUT=ON or OFF. In addition:

• You cannot set the ERROROUT=filename parameter in an ON TABLE SET command.
• ERROROUT settings apply only to batch operations.

STRREP (inlength, instring, searchlength, searchstring, replength,  
repstring, outlength, outstring)

where:

inlength

Numeric

Is the number of characters in the input string.

instring

Alphanumeric

Is the input string.

searchlength

Numeric

Is the number of characters in the (shorter length) string to be replaced.

searchstring

Alphanumeric

Is the character string to be replaced.

replength

Numeric

Is the number of characters in the replacement string. Must be zero (0) or greater. Zero is used to delete a character from a string.

repstring

Alphanumeric

Is the replacement string (alphanumeric). Ignored if replength is zero (0).

outlength

Numeric

Is the number of characters in the resulting output string. Must be 1 or greater.

outstring

Alphanumeric

Is the resulting output string after all replacements and padding.

The maximum string length is 4095.

In the following example, STRREP finds and replaces commas and dollar signs that appear in the CS_ALPHA field, first replacing commas with null strings to produce CS_NOCOMMAS (removing the commas) and then replacing the dollar signs ($) with (USD) in the right-most CURR_SAL column:

TABLE FILE EMPLOYEE
SUM CURR_SAL NOPRINT
COMPUTE CS_ALPHA/A15=FTOA(CURR_SAL,'(D12.2M)',CS_ALPHA);
        CS_NOCOMMAS/A14=STRREP(15,CS_ALPHA,1,',',0,'X',14,CS_NOCOMMAS);
        CS_USD/A17=STRREP(14,CS_NOCOMMAS,1,'$',4,'USD ',17,CS_USD);
        NOPRINT
        CS_USD/R AS CURR_SAL
BY LAST_NAME
END

The output is:

LAST_NAME        CS_ALPHA         CS_NOCOMMAS              CURR_SAL
---------        --------         -----------     -----------------
BANNING               $29,700.00       $29700.00       USD 29700.00
BLACKWOOD             $21,780.00       $21780.00       USD 21780.00
CROSS                 $27,062.00       $27062.00       USD 27062.00
GREENSPAN              $9,000.00        $9000.00        USD 9000.00
IRVING                $26,862.00       $26862.00       USD 26862.00
JONES                 $18,480.00       $18480.00       USD 18480.00
MCCOY                 $18,480.00       $18480.00       USD 18480.00
MCKNIGHT              $16,100.00       $16100.00       USD 16100.00
ROMANS                $21,120.00       $21120.00       USD 21120.00
SMITH                 $22,700.00       $22700.00       USD 22700.00
STEVENS               $11,000.00       $11000.00       USD 11000.00

SET COMPMISS = {ON|OFF}

where:

ON

Propagates a missing value to a reformatted field. ON is the default value.

OFF

Displays a blank or zero for a reformatted field.

Note: The COMPMISS parameter cannot be set in an ON TABLE command.

The following procedure prints the RETURNS field from the SALES data source for store 14Z. With COMPMISS OFF, the missing values display as zeros in the column for the reformatted field value. (Note: Before trying this example, you must make sure that the SALEMISS procedure, which adds missing values to the SALES data source, has been run.)

SET COMPMISS = OFF
TABLE FILE SALES
PRINT RETURNS RETURNS/D12.2 AS 'REFORMATTED,RETURNS'
BY STORE_CODE
WHERE STORE_CODE EQ '14Z'
END

The output is:

                        REFORMATTED
STORE_CODE  RETURNS     RETURNS    
----------  -------     -----------
14Z               2            2.00
                  2            2.00
                  0             .00
                  .             .00
                  4            4.00
                  0             .00
                  3            3.00
                  4            4.00
                  .             .00
                  4            4.00

With COMPMISS ON, the column for the reformatted version of RETURNS displays the missing data symbol when a value is missing:

SET COMPMISS = ON
TABLE FILE SALES
PRINT RETURNS RETURNS/D12.2 AS 'REFORMATTED,RETURNS'
BY STORE_CODE
WHERE STORE_CODE EQ '14Z'
END

The output is:

                        REFORMATTED
STORE_CODE  RETURNS     RETURNS
----------  -------     -----------
14Z               2            2.00
                  2            2.00
                  0             .00
                  .               .
                  4            4.00
                  0             .00
                  3            3.00
                  4            4.00
                  .               .
                  4            4.00

If you create a HOLD file with COMPMISS ON, the HOLD Master File for the reformatted field indicates MISSING = ON (as does the original field). With COMPMISS = OFF, the reformatted field does NOT have MISSING = ON in the generated Master File.

SET DBACSENSITIV = {ON|OFF}

where:

ON

Does not convert passwords to upper case. All comparisons between the password set by the user and the password in the Master File or FOCEXEC are case sensitive.

OFF

Converts passwords to upper case prior to validation. All comparisons between the password set by the user and the password in the Master File or FOCEXEC are not case sensitive. OFF is the default value.

Consider the following DBA declaration added to the EMPLOYEE Master File:

USER = User2, ACCESS = RW,$

User2 wants to report from the EMPLOYEE data source and issues the following command:

SET USER = USER2

With DBACSENSITIV OFF, User2 can run the request even though the case of the password entered does not match the case of the password in the Master File.

With DBACSENSITIV ON, User2 gets the following message:

(FOC047) THE USER DOES NOT HAVE SUFFICIENT ACCESS RIGHTS TO THE FILE:

With DBACSENSITIV ON, the user must issue the following command:

SET USER = User2

Note: In FOCUS for Mainframe, all user input is transmitted in upper case. Therefore, a mixed case password cannot be issued at the command line. It must be set in a FOCEXEC or profile.