Working with Variables Part I: Building Run-Time Prompted Views with Sequel

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:

SELECT   cname,amtdu,pdate

FROM    sequelex/custmast

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.

Image

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.

Image

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:

Image

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) 

FROM sequelex/ordhead

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

FROM     sequelex/custmast,sequelex/ordhead

INNER JOIN   cusno.custmast=cusno.ordhead

WHERE   otype2=&otype2

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

Image

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.

Image

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.

Image

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 >