Data access and analysis tools that utilize runtime prompts allow you to retrieve information faster and easier.

Written by Heath Kath

Do you struggle to keep track of countless, nearly identical queries that differ only in selection criteria like region, department, and date range? Runtime prompts help you stay organized by allowing different users and departments to run the same views but return unique, tailored results.

SEQUEL lets you create prompted views quickly. For example, 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 and to set variable values for batch and scheduled jobs dynamically.

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 "&&pdate1" and "&&pdate2"

In this example, the view will select records whose dates fall between the dates specified by your runtime variables (&&pdate1, &&pdate2). 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. 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.

Figure 1: This images shows the variable definition.

When end users run the view, a runtime prompt window appears. The user can accept the default value, enter a date, or select a different start date from the drop-down calendar. The expression or field should return a character, zoned, or date field.

Figure 2: Here's what the runtime prompt window looks like.

Other expression examples:

• 1 week ago: SQL(current date - 7 days)
• Current year: SQL(ZONED(year(Current date),4,0))
• 1^st of the month: SQL(cvtdate(year(current date),month(current date),01))
• 1^st 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)

How to Use a System Value as a Default Value

The default value can use keywords to retrieve systems values as follows:

Figure 3: These are the system keywords in SEQUEL.

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."

1. 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(20)

FROM sequelex/ordhead

The new field value of DESC can now become a default value in another view.

2. 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.

Figure 4: Note the variable definition.

When you run the DFTORDEX1 view, the runtime prompt allows you to enter an order type or accept the default value from the DFTOTYPE view.

Figure 5: And here's the runtime prompt window.

How to Set Up a *NOPROMPT with Runtime Variables

When you use a *NOPROMPT variable, users do not have to designate a value at runtime. This prompted view can then run in a batch job or from a scheduled job.

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.

Figure 6: With *NOPROMPT, the job number default is hidden.

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 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(This email address is being protected from spambots. You need JavaScript enabled to view it.')

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")

For more on SEQUEL Software, provider of the leading data access solution for Power Systems running IBM i, visit www.sequel-software.com/products.

Heath Kath is a Technical Solutions Consultant specializing in business intelligence, data access and analysis, and report distribution. Heath is a Robot and IBM Certified Specialist with more than 20 years of System i experience, including 11 years at IBM working in product support, testing, software support, and training. His background includes CL and RPG programming, Query/400, Query Manager, SQL, and journaling. Heath hosts a bi-weekly webinar for business intelligence products and provides technical support for new customers.