Use expressions or view names for default values

When you create a SEQUEL view, you can use expressions or view names as default values for run-time prompts. By doing this, you can make it easier for end users to run requests for commonly selected variables.

Using an expression to create a default
For example, suppose you want to specify a date range for a SEQUEL view or report that automatically includes the last year of activity. You can use an expression in the default value to calculate the year-ago date as the starting value. Look at following view created using the CUSTMAST file in the SEQUELEX library:

SELECT    cname,amtdu,pdate

FROM    SEQUELEX/CUSTMAST

WHERE    pdate between “&&pdate1″ and “&&pdate2″

This view selects records between runtime variables for a starting date (&&pdate1) and an ending date (&&pdate2). If you don’t specify a default value, both date values are defined automatically as the current date, forcing the end user to scroll through the calendar prompt or type in date values. By adding the expression sql(current date – 1 year) as the default value (below), the view automatically displays the date from a year ago for the starting date.

When the end user runs this view, a run-time prompt appears. The user can accept the default value or select another starting date.

Using another view to create a default
You also can reference another SEQUEL view to provide a default value. For example, you might have a simple view named DFTEX1 that produces a list of customer numbers with a value greater than 200,000:

SELECT    cusno

FROM    sequelex/custmast

WHERE    cusno>200000

ORDER BY    cusno

The results for this view contain multiple records. Let’s look at how to use this view to provide default values for two different examples.

1. First, create another view called DFTEX2:

SELECT    cusno, cname, cstte

FROM    sequelex/custmast

WHERE    cusno=&cusno

Note: When you define the variable &CUSNO, refer to the DFTEX1 view created earlier for the default value.

2. When you run the DFTEX2 view, the prompt will contain customer number 200,112, the first customer number in the CUSTMAST file with a value greater than 200,000.

3. Now, modify the DFTEX2 view by adding a DBlist integrity test for the &CUSNO variable. The DBlist function produces a list of all valid customer numbers in the CUSTMAST file.

4. When you run the DFTEX2 view, the runtime prompt provides a drop-down selection list containing all customer numbers, but the default is set to the value provided by the DFTEX1 view.

Contributed by Steven Smith, Technical Writer

This entry was posted on Wednesday, November 19th, 2008 at 10:05 am and is filed under SEQUEL. You can follow any responses to this entry through the RSS 2.0 feed. Both comments and pings are currently closed.