In this section: How to: Reference: |
DEFINE is an optional attribute used to create a virtual field for reporting. You can derive the virtual field value from information already in the data source (that is, from permanent fields). Some common uses of virtual data fields include:
Virtual fields are available whenever the data source is used for reporting.
DEFINE fieldname/format [REDEFINES field2] = expression; [, attribute2, ... ] $
where:
Is the name of the virtual field. You can assign any name up to 66 characters long. The name is subject to the same conventions as names assigned using the FIELDNAME attribute. FIELDNAME is described in The Field Name: FIELDNAME.
Is the field format. It is specified in the same way as formats assigned using the USAGE attribute, which is described in The Displayed Data Type: USAGE. If you do not specify a format, it defaults to D12.2.
Enables you to redefine or recompute a field whose name exists in more than one segment.
Is a valid expression. Expressions are fully described in the Creating Reports manual. The expression must end with a semicolon (;).
Note that when an IF-THEN phrase is used in the expression of a virtual field, it must include the ELSE phrase.
The declaration for a virtual field can include additional optional attributes, such as TITLE and DESCRIPTION.
Place each DEFINE attribute after all of the field descriptions for that segment.
The following shows how to define a field called PROFIT in the segment CARS:
SEGMENT = CARS ,SEGTYPE = S1 ,PARENT = CARREC, $ FIELDNAME = DEALER_COST ,ALIAS = DCOST ,USAGE = D7, $ FIELDNAME = RETAIL_COST ,ALIAS = RCOST ,USAGE = D7, $ DEFINE PROFIT/D7 = RETAIL_COST - DEALER_COST; $
Note the following rules when using DEFINE:
A DEFINE attribute cannot contain qualified field names on the left-hand side of the expression. Use the WITH phrase on the left-hand side to place the defined field in the same segment as any real field you choose. This will determine when the DEFINE expression will be evaluated.
Expressions on the right-hand side of the DEFINE can refer to fields from any segment in the same path. The expression on the right-hand side of a DEFINE statement in a Master File can contain qualified field names.
A DEFINE attribute in a Master File can refer to only fields in its own path. If you want to create a virtual field that derives its value from fields in several different paths, you have to create it with a DEFINE FILE command using an alternate view prior to a report request, as discussed in the Creating Reports manual. The DEFINE FILE command is also helpful when you wish to create a virtual field that is only used once, and you do not want to add a declaration for it to the Master File.
Virtual fields defined in the Master File are available whenever the data source is used, and are treated like other stored fields. Thus, a field defined in the Master File cannot be cleared in your report request.
A virtual field cannot be used for cross-referencing in a join. It can, however, be used as a host field in a join.
Note: Maintain does not support DEFINE attributes that have a constant value. Using such a field in a Maintain procedure generates the following message:
(FOC03605) name is not recognized.Reference: |
Master File DEFINE fields can use Dialogue Manager system date variables to capture the system date each time the Master File is parsed for use in a request.
The format of the returned value for each date variable is the format indicated in the variable name. For example, &DATEYYMD returns a date value with format YYMD. The exceptions are &DATE and &TOD, which return alphanumeric values and must be assigned to a field with an alphanumeric format. The variable names &DATE and &TOD must also be enclosed in single quotation marks in the DEFINE expression.
The variables supported for use in Master File DEFINEs are:
Note that all other reserved amper variables are not supported in Master Files.
The following version of the EMPLOYEE Master File has the DEFINE field named TDATE added to it. TDATE has format A12 and retrieves the value of &DATE, which returns an alphanumeric value and must be enclosed in single quotation marks:
FILENAME=EMPLOYEE, SUFFIX=FOC SEGNAME=EMPINFO, SEGTYPE=S1 FIELDNAME=EMP_ID, ALIAS=EID, FORMAT=A9, $ FIELDNAME=LAST_NAME, ALIAS=LN, FORMAT=A15, $ FIELDNAME=FIRST_NAME, ALIAS=FN, FORMAT=A10, $ FIELDNAME=HIRE_DATE, ALIAS=HDT, FORMAT=I6YMD, $ FIELDNAME=DEPARTMENT, ALIAS=DPT, FORMAT=A10, $ FIELDNAME=CURR_SAL, ALIAS=CSAL, FORMAT=D12.2M, $ FIELDNAME=CURR_JOBCODE, ALIAS=CJC, FORMAT=A3, $ FIELDNAME=ED_HRS, ALIAS=OJT, FORMAT=F6.2, $ DEFINE TDATE/A12 ='&DATE';, $ . . .
The following request displays the value of TDATE:
TABLE FILE EMPLOYEE PRINT LAST_NAME FIRST_NAME HIRE_DATE TDATE AS 'TODAY''S,DATE' WHERE LAST_NAME EQ 'BANNING' END
The output is:
TODAY'S LAST_NAME FIRST_NAME HIRE_DATE DATE --------- ---------- --------- ------- BANNING JOHN 82/08/01 05/11/04
The following version of the EMPLOYEE Master File has the DEFINE field named TDATE added to it. TDATE has format YYMD and retrieves the value of &YYMD:
FILENAME=EMPLOYEE, SUFFIX=FOC SEGNAME=EMPINFO, SEGTYPE=S1 FIELDNAME=EMP_ID, ALIAS=EID, FORMAT=A9, $ FIELDNAME=LAST_NAME, ALIAS=LN, FORMAT=A15, $ FIELDNAME=FIRST_NAME, ALIAS=FN, FORMAT=A10, $ FIELDNAME=HIRE_DATE, ALIAS=HDT, FORMAT=I6YMD, $ FIELDNAME=DEPARTMENT, ALIAS=DPT, FORMAT=A10, $ FIELDNAME=CURR_SAL, ALIAS=CSAL, FORMAT=D12.2M, $ FIELDNAME=CURR_JOBCODE, ALIAS=CJC, FORMAT=A3, $ FIELDNAME=ED_HRS, ALIAS=OJT, FORMAT=F6.2, $ DEFINE TDATE/YYMD = &YYMD ;, $ . . .
The following request displays the value of TDATE:
TABLE FILE EMPLOYEE PRINT LAST_NAME FIRST_NAME HIRE_DATE TDATE AS 'TODAY''S,DATE' WHERE LAST_NAME EQ 'BANNING' END
The output is:
TODAY'S LAST_NAME FIRST_NAME HIRE_DATE DATE --------- ---------- --------- ------- BANNING JOHN 82/08/01 2004/05/11
| Information Builders |