ARGFR

Within an OLAP DML program, the ARGFR function lets you reference the arguments that are passed to a program. The function returns a group of one or more arguments, beginning with the specified argument number, as a single text value. You can use ARGFR only within a program that is invoked as a command, not as a user-defined function or with a CALL statement.

Note:

Use an ARGUMENT statement to define arguments in a program and to negate the need for using the ARGFR function to reference arguments passed to the program. For more information on how to use the ARGUMENT to define arguments that are passed to a program, see "Declaring Arguments that Will be Passed Into a Program" .

Important:

When you want to pass NTEXT arguments, be sure to declare them using ARGUMENT instead of using ARGFR. With ARGFR, NTEXT arguments are converted to TEXT, and this can result in data loss when the NTEXT values cannot be represented in the database character set.

Return Value

TEXT

Syntax

ARGFR(n)

Arguments

n: The number by position of the first argument in the group of arguments you want to reference. ARGFR(1) returns the first argument and all subsequent arguments, ARGFR(2) returns the second argument and all subsequent arguments, and so forth. When there are fewer than n arguments, ARGFR returns a null value. ARGFR also returns a null value when n is 0 (zero) or negative.

Notes

Ampersand Substitution

The ARGFR function is often preceded by an ampersand (&) in a program line to allow flexibility in specifying arguments; in other words, to tell Oracle OLAP not to pass the literal contents of ARGFR into the program, but what the contents point to. See "Passing Arguments Using ARG and ARGFR".

Argument Requirements

When a program is invoked as a command -- that is, without parentheses around the arguments -- Oracle OLAP counts each word and punctuation mark on the command line as a separate argument. Therefore, you cannot include arithmetic expressions, functions, qualified data references, or IF...THEN...ELSE... statements as arguments in the program.

When you want to include any of these types of expressions as arguments in a program invoked as a command, you must include a PARSE statement in the program.

ARG and ARGS Functions

To reference a single argument, use ARG, or to reference all the arguments, use ARGS.

ARGCOUNT Function

A program can include ARGCOUNT to verify the number of arguments passed to the program.

Commas or Spaces as Delimiters

In most cases, you can use either commas or spaces between arguments. However, arguments may need to be separated with commas when those arguments include parentheses or negative numbers. Without commas, Oracle OLAP might interpret parenthetical expressions as qualified data references and negative signs as subtraction.

CALLTYPE Function

You can use CALLTYPE to determine whether a program was invoked as a function, as a command, or by using CALL.

Examples

Example 8-5 Passing Arguments Using ARG and ARGFR

Suppose you have a program that produces a sales report. You want to be able to produce this report for any product and any period of months, so you do not want to limit the product and month dimensions to specific values in the program. Instead, you can use the LIMIT command using ARG for the product argument and an ARGFR function for the month argument. This way, these items can be specified when the program is run.

When ARGFR is included in the LIMIT command preceded by an ampersand (&), Oracle OLAP substitutes the values of &ARGFR before the command is executed and, as a result, treats the whole argument as a phrase of the LIMIT command. The salesreport program has a LIMIT command that includes &ARGFR.

DEFINE salesrpt PROGRAM
PROGRAM
PUSH product month district
TRAP ON cleanup
LIMIT product TO UPCASE(ARG(1))
LIMIT month TO &ARGFR(2)
LIMIT district TO ALL
REPORT grandtotals DOWN district sales
cleanup:
POP product month district
END

The command line for the salesrpt program must include two or more arguments. The first argument is the product for the report, and the second and subsequent arguments are the months. In the LIMIT month statement, the &ARGFR(2) function returns the months that were specified as arguments on the command line.

The following statement executes the salesrpt program, specifying Jan96, Feb96, Mar96, and Apr96 for the values of month.

salesrpt 'Canoes' 'Jan96' TO 'Apr96'

The statement produces the following output.

PRODUCT: CANOES
        -------------------SALES------------------
        -------------------MONTH------------------
DISTRICT    Jan96     Feb96      Mar96     Apr96   
------- ---------- ---------- ---------- --------- 
Boston  70,489.44  82,237.68  97,622.28 134,265.60 
Atlanta 56,271.40  61,828.33  77,217.62 109,253.38 
Chicago 48,661.74  54,424.94  68,815.71  93,045.46 
Dallas  35,244.72  40,218.43  46,810.68  64,031.28 
Denver  44,456.41  50,623.19  57,013.01  78,038.13 
Seattle 67,085.12  74,834.29  87,820.04 119,858.56 
       ---------- ---------- ---------- ---------- 
       322,208.83 364,166.86 435,299.34 598,492.41 
       ========== ========== ========== ==========

The following statement specifies the first three months of 1996.

salesrpt 'Tents' quarter 'Q1.96'

The statement produces the following output.

PRODUCT: TENTS
               -------------SALES-------------
               -------------MONTH-------------
DISTRICT          Jan96     Feb96      Mar96
-------------- ---------- ---------- ---------
Boston         50,808.96  34,641.59  45,742.21
Atlanta        46,174.92  50,553.52  58,787.82
Chicago        31,279.78  31,492.35  42,439.52
Dallas         50,974.46  53,702.75  71,998.57
Denver         35,582.82  32,984.10  44,421.14
Seattle        45,678.41  43,094.80  54,164.06
              ----------  ---------- ---------
              260,499.35 246,469.11 317,553.32
              ========== ========== ==========