ARG

Within an OLAP DML program, the ARG function lets you reference arguments passed to a program. The function returns one argument as a text value.

Note:

Use an ARGUMENT statement to define arguments in a program and to negate the need for using the ARG 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 ARG. With ARG, 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

ARG(n)

Arguments

n: The number by position of the argument whose value you want to reference. ARG(1) returns the first argument to the program, ARG(2) returns the second argument, and so forth. When the program is called with fewer than n arguments, ARG returns a null value. ARG also returns a null value when n is zero or negative.

Notes

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 use ARG when the arguments include arithmetic expressions, functions, qualified data references, or IF...THEN...ELSE statements as arguments.

When you want to include any of these types of expressions in the arguments, you can invoke the program in one of the following ways:

Invoke it as a command. With this method, the program must handle the arguments as a text expression, perhaps using ARGS, and it must use PARSE to interpret the arithmetic expressions, functions, qualified data references, and IF...THEN...ELSE statements.
Invoke it as a user-defined function or with CALL and enclose the arguments within parentheses. When you use CALL, the return value is discarded.

Arguments Passed by Value

The ARG function is often preceded by an ampersand (&) in a program line to allow the user flexibility in specifying arguments; in other words, to tell Oracle OLAP not to pass the literal contents of ARG into the program, but what the contents point to. Another way to pass arguments by value is to declare them using an ARGUMENT statement instead of referencing them with the ARG function.

ARGS and ARGFR Functions

To reference all the arguments, or a group of arguments, use ARGS or ARGFR.

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 a CALL statement.

Examples

Example 8-3 Assigning Arguments

Suppose you have a program that produces a sales report. You want to be able to produce this report for any two periods of months, so you do not want to limit the month dimension to any particular month in the program. Instead, you use ARG functions in the LIMIT command so that the starting and ending months for the two periods can be supplied as arguments when the program is run.

Notice the UPCASE function preceding the ARG functions. UPCASE allows the arguments to be specified in upper- or lowercase, even though dimension values in the analytic workspace are in uppercase. A prefixed & (ampersand) would have a similar effect since it tells Oracle OLAP to substitute the values of ARG before the LIMIT command is executed -- in this case, a value of the month dimension. However, an & (ampersand) has the disadvantage of preventing compilation of program lines in which it appears, and slower execution results.

DEFINE salesrpt PROGRAM
PROGRAM
PUSH month product district
TRAP ON cleanup 
LIMIT month TO UPCASE(ARG(1)) TO UPCASE(ARG(2))
LIMIT product TO 'CANOES'
LIMIT district TO all
REPORT grandtotals DOWN district sales
LIMIT month TO UPCASE(ARG(3)) TO UPCASE(ARG(4))
REPORT grandtotals DOWN district sales
cleanup:
POP month product district
END

To run the program, you specify the program name (salesrpt) followed by two sets of months to mark the beginning and the end of the two periods of sales to be reported. Then, when the LIMIT MONTH statements are executed, Oracle OLAP passes the months specified on the command line as return values for ARG(1), ARG(2), ARG(3), and ARG(4) in the LIMIT commands.

salesrpt 'Jan95' 'Mar95' 'Jan96' 'Mar96'

This statement produces the following output.

PRODUCT: Canoes
              ------------SALES--------------
              ------------MONTH--------------
DISTRICT        Jan95       Feb95     Mar95
---------------------------------------------
Boston        66,013.92  76,083.84  91,748.16
Atlanta       49,462.88  54,209.74  67,764.20
Chicago       45,277.56  50,595.75  63,576.53
Dallas        33,292.32  37,471.29  43,970.59
Denver        45,467.80  51,737.01  58,437.11
Seattle       64,111.50  71,899.23  83,943.86
             ----------  ---------  ---------
             303,625.98 341,996.86 409,440.44
             ========== ========== ==========
PRODUCT: Canoes
             ------------SALES---------------
             ------------MONTH---------------
DISTRICT        Jan96      Feb96       Mar96
---------------------------------------------
Boston        70,489.44  82,237.68  97,622.28
Atlanta       56,271.40  61,828.33  77,217.62
Chicago       48,661.74  54,424.94  68,815.71
Dallas        35,244.72  40,218.43  46,810.68
Denver        44,456.41  50,623.19  57,013.01
Seattle       67,085.12  74,834.29  87,820.04
             ----------  ---------  ---------
             322,208.83 364,166.86 435,299.35
             ========== ========== ==========