|
How to: |
|
Reference: |
You can define global variables in a Master File and use them to parameterize certain attributes in the Master File and its corresponding Access File. For example, you can parameterize the connection attribute in the Access File with a variable you define in the Master File and then specify the actual connection name at run time.
Add variable definitions after the FILE declaration in the Master File:
VARIABLE NAME=[&&]var, USAGE=Aln, [DEFAULT=defvalue,][QUOTED={OFF|ON},]$
where:
Is the name you are assigning to the global variable. When you reference the variable in the Master or Access File, you must prepend the name with two ampersands. However, the ampersands are optional when defining the variable.
Is the maximum length for the variable value.
Is the default value for the variable. If no value is set at run time, this value is used.
ON adds single quotation marks around the assigned string for the variable. A single quotation mark within the string is converted to two single quotation marks. OFF is the default value.
In the Master File, the following attributes can be parameterized with variables: POSITION, OCCURS, REMARKS, DESCRIPTION, TITLE, HELPMESSAGE.
In the DBA section of a Master File, the following attributes can be parameterized: USER, VALUE. For information about using these variables in a Master File profile to create dynamic DBA rules, see Creating and Using a Master File Profile.
In the Access File, the following attributes can be parameterized with variables: CONNECTION, TABLENAME, WORKSHEET (Excel via Direct Retrieval) START, CHKPT_SAVE, CHKPT_FILE, POLLING, TIMEOUT, MAXLUWS, ACTION, MSGLIMIT, DIRECTORY, NAME, EXTENSION, DATA_ORIGIN, MAXFILES, MAXRECS, OBJECT, PICKUP, TRIGGER, DISCARD, ARCHIVE.
Note: You can concatenate multiple variables to create an attribute value.
The following request creates an Oracle table named ORAEMP from the FOCUS data source named EMPLOYEE:
TABLE FILE EMPLOYEE SUM LAST_NAME FIRST_NAME CURR_SAL CURR_JOBCODE DEPARTMENT BY EMP_ID ON TABLE HOLD AS ORAEMP FORMAT SQLORA END
The following is the Master File created by the request:
FILENAME=ORAEMP , SUFFIX=SQLORA , $
SEGMENT=SEG01, SEGTYPE=S0, $
FIELDNAME=EMP_ID, ALIAS=EID, USAGE=A9, ACTUAL=A9, $
FIELDNAME=LAST_NAME, ALIAS=LN, USAGE=A15, ACTUAL=A15, $
FIELDNAME=FIRST_NAME, ALIAS=FN, USAGE=A10, ACTUAL=A10, $
FIELDNAME=CURR_SAL, ALIAS=CSAL, USAGE=D12.2M, ACTUAL=D8, $
FIELDNAME=CURR_JOBCODE, ALIAS=CJC, USAGE=A3, ACTUAL=A3, $
FIELDNAME=DEPARTMENT, ALIAS=DPT, USAGE=A10, ACTUAL=A10, $
The following is the Access File created by the request:
SEGNAME=SEG01, TABLENAME=ORAEMP, KEYS=01, WRITE=YES, $
Add the following variable definitions to the Master File in order to parameterize the TABLENAME attribute in the Access File and the TITLE attribute for the EMP_ID column in the Master File:
FILENAME=ORAEMP, SUFFIX=SQLORA , $ VARIABLE NAME=table, USAGE=A8, DEFAULT=EDUCFILE, $ VARIABLE NAME=emptitle, USAGE=A30, DEFAULT=empid,$
Now, in the Master File, add the TITLE attribute to the FIELD declaration for EMP_ID:
FIELDNAME=EMP_ID, ALIAS=EID, USAGE=A9, ACTUAL=A9,
TITLE='&&emptitle', $
In the Access File, replace the value for the TABLENAME attribute with the variable name:
SEGNAME=SEG01, TABLENAME=&&table, KEYS=01, WRITE=YES, $
The following request sets the values of the variables and then issues a TABLE request:
-SET &&table = ORAEMP; -SET &&emptitle = 'Id,number'; TABLE FILE ORAEMP PRINT EMP_ID LAST_NAME FIRST_NAME DEPARTMENT END
Note that the value for &&emptitle is enclosed in single quotation marks in the --SET command because it contains a special character (the comma). The single quotation marks are not part of the string and do not display on the report output. The column title would display enclosed in single quotation marks if the variable definition contained the attribute QUOTED=ON.
On the report output, the column title for the employee ID column displays the value set for &&emptitle, and the table accessed by the request is the ORAEMP table created as the first step in the example:
Id number LAST_NAME FIRST_NAME DEPARTMENT ------ --------- ---------- ---------- 071382660 STEVENS ALFRED PRODUCTION 112847612 SMITH MARY MIS 117593129 JONES DIANE MIS 119265415 SMITH RICHARD PRODUCTION 119329144 BANNING JOHN PRODUCTION 123764317 IRVING JOAN PRODUCTION 126724188 ROMANS ANTHONY PRODUCTION 219984371 MCCOY JOHN MIS 326179357 BLACKWOOD ROSEMARIE MIS 451123478 MCKNIGHT ROGER PRODUCTION 543729165 GREENSPAN MARY MIS 818692173 CROSS BARBARA MIS
In the following example, the TABLENAME attribute requires a multipart name consisting of a database name, an owner ID, a table prefix, and a static table name with a variable suffix. In this case, you can define separate variables for the different parts and concatenate them.
First, define separate variables for each part:
VARIABLE NAME=db,USAGE=A8,DEFAULT=mydb,$ VARIABLE NAME=usr,USAGE=A8,DEFAULT=myusrid,$ VARIABLE NAME=tprf,USAGE=A4,DEFAULT=test_,$ VARIABLE NAME=tsuf,USAGE=YYM,$
In the Access File, concatenate the variables to create the TABLENAME attribute. Note that the separator for between each part is a period, but to concatenate a variable name and retain the period, you must use two periods:
TABLENAME=&db..&usr..&tprf.table&tsuf,
Based on the defaults, the TABLENAME would be:
TABLENAME=mydb.myusrid.test_table
In a request, set the following values for the separate variables:
I-SET &&db=db1; -SET &&tprf=prod_; -SET &&tsuf=200801;
With these values, the TABLENAME used is the following:
TABLENAME=db1.myusrid.prod_table200801