Do you struggle to keep track of countless, nearly-identical queries that differ only in selection criteria like region, department, and date range? Then you could use run-time prompts.
Run-time prompts help you stay organized by allowing different users and departments to run the same views, but return unique, tailored results.
With Sequel Data Access, you can create prompted views quickly. Watch the 4-minute video to see how.
Here's how: when selecting default values, you can choose expressions from a SQL statement or view, or you can use fields from a database file. This makes it easy for end users to run requests for commonly selected variables. Plus, it allows you to set variable values for batch and scheduled jobs dynamically.
Not using Sequel? Request a demo to see how run-time prompts work.
Already using Sequel? Here are four tips and tricks for building run-time prompted views.
1. How to Create a Default Value Using an Expression or System Value
Suppose you’re creating a Sequel view or report that automatically includes data from the last year of activity. You can use an expression in the default value to determine the date one year ago and use it as your start value. The following view was created using the CUSTMAST file in the SEQUELEX library:
WHERE pdate between "&&startdate" and "&&enddate"
In this example, our view will select records whose dates fall between the dates specified by your run-time variables (&&startdate, &&enddate).
If you don’t specify a default value, both date values will be defined as the current date, which will force end users to choose dates from a calendar or enter values manually.
The expression or field should return a character, zoned, or date field. By adding the expression SQL(current date - 1 year) as your default, the view automatically uses the date from one year ago for its start date.
When end users run the view, a run-time prompt window appears. The user can accept the default value, enter a date, or select a different start date from the drop-down calendar.
Other expression examples:
1 week ago: SQL(current date - 7 days)
Current year: SQL(ZONED(year(Current date),4,0))
1st of the month: SQL(cvtdate(year(current date),month(current date),01))
1st of last month: SQL(cvtdate(year(current date-1 month), month(current date-1 month), 01))
End of last month: SQL(cvtdate(year(current date), month(current date), 01)- 1 day)
2. How to Use a System Value as a Default Value
The default value can use keywords to retrieve systems values as follows:
3. How to Create Default Values Using a View
You can also harvest default values from Sequel views. This is especially useful when selection criteria is specific, or when the expression for the default value is complex. For example, you might want to describe a code field from your file conditionally, so the default value appears as “Backorder” instead of “B.”
First, create a view with the CASE statement (DFTOTYPE):
SELECT CASE WHEN otype="B" THEN "Backorder" WHEN otype="O" THEN "Blanket" WHEN otype="R" THEN "Regular" ELSE "Invalid Type" END NAME(DESC) LEN(12)
The new field value of DESC can now become a default value in another view.
Second, create a prompted view called DFTORDEX1:
SELECT cusno.custmast, cname.custmast, CASE WHEN otype="B" THEN "Backorder" WHEN otype="O" THEN "Blanket" WHEN otype="R" THEN "Regular" ELSE "Invalid Type" END NAME(otype2), invno.ordhead
INNER JOIN cusno.custmast=cusno.ordhead
Note: When you define the variable &OTYPE2, refer to the DFTOTYPE view you created earlier for the default value.
When you run the DFTORDEX1 view, the run-time prompt allows you to enter an order type or accept the default value from the DFTOTYPE view.
4. How to Set Up a *NOPROMPT with Run-Time Variables
When you use a *NOPROMPT variable, users do not have to designate a value at run time. This prompted view can then run in a batch job or from a scheduled job. Using *NOPROMPT allows you to dynamically capture a system value or a calculated value and pass it into the SQL statement of the view or a command string through a script.
You can add the Prompt Text value *NOPROMPT to the variable definition in order to suppress the variable entry box in the prompt window as shown below. The values *NP and *NOPMT also work.
Note: Be careful not to overuse *NOPROMPT. Use it only when you are able to use a default value, and when users should not be prompted.
Command examples running within a Sequel Script from a batch job (all variables defined as *NOPROMPT)
- EXECUTE VIEW(userlib/userview) PCFMT(*PDF) TOSTMF('/home/sqclass/custf.xls') REPLACE(*YES)
- REPORT REPORT(userlib/userrpt) PCFMT(*PDF) TOSTMF('/home/salesfldr/&&FILENM') RECIPIENT([email protected]')
The examples above have variables defined within the views as *NOPROMPT. The second example also uses a no-prompt variable to build a PDF filename based on the date.
Variable Default: SQL("File_" CAT char(current date, ISO) CAT ".pdf")
Continue learning about variables. Read Working with Variables Part II: Taking Sequel Run-Time Prompts to the Next Level >
Want to get more tips for using run-time prompts? Sign up Sequel training.