Migrated ShowCase Queries with Null Capable Variables

 

Overview

ShowCase 9x (and earlier) queries can be designed with variables that use blanks as a Null capable default value. By default all new variables added to a query are set as Null Capable. This allows the query to run without a value and, in effect, return all records for the variable.

ShowCase 10 query objects (views) do not apply this feature automatically in versions prior to R10M14 and 10.20.174. Instead the views must be designed to use the reserved keyword of *ALL (or *OMIT) along with specified leading and trailing text to achieve the same result.

Null Capable ShowCase queries will migrate using the C&DS Migration Utility, or the ViewPoint Import ShowCase Query feature. Beginning with ShowCase R10M03, the Migration Utility (and the ViewPoint Import
ShowCase Query feature) will migrate Null Capable variables in most instances, and the view/query logic will be adapted to use a *OMIT/*ALL technique.

Warning messages will be added to the migration log in the event of a translation code issue. Verification should be done along with a review of the necessity of using Null capable variables. In many cases, it is a best practice
to make a record selection mandatory so meaningful results are returned instead of all records. 

Consider the following example ShowCase query:

Example

Here is the SQL from a ShowCase query containing two variables—one for a state value (&STATE) and the other (two really) for a date range (&ENTRY_DATE).

In the example that follows, the migrated ViewPoint view will be modified so the date variables can be run to return all dates for a given state.

Steps

1. Open the migrated view in the ViewPoint view designer. Notice that except for the file-library qualification, and the renaming of the second occurrence of the Entry_Date variable name, the SQL is very similar to the original ShowCase version. In fact, the view will run and prompt for input. It just won't return all records like it used to (yet).
   
   The way ViewPoint achieves the effect of returning all values for a variable is to omit the variable and any leading or trailing SQL text associated with the variable. The variable and text are removed (omitted) from the SQL at run-time thereby returning all the records.
   
   

   If you focus on the WHERE clause above we see:

   WHERE 1 = 1 AND STATE IN(&STATE) AND ENTRY_DATE BETWEEN ‘&&ENTRY_DATE’ AND ‘&&ENTRY_DAT1’

   
   

   At run time, if the reserved values *ALL or *OMIT are entered in place of date values, the SQL will look like so:

   WHERE 1 = 1 AND STATE IN(&STATE) AND ENTRY_DATE BETWEEN ‘’ AND ‘’

   
   Although the variables have been removed, the SQL syntax is wrong and the view will error. For each variable the leading and trailing (SQL) text must be defined so they can be removed along with the variables.
   
   
2. Select the Variables tab and notice the migration process added the necessary leading and trailing. You can click in any of the entry boxes to make changes. For the two date variables the Default Value is set to *ALL. Starting with the ENTRY_DATE variable, the Omit Leading Text is set to literally AND ENTRY_DATE BETWEEN ‘ (including the single quote) and the Omit Trailing Text is set to a single quote (‘).
   Note: The Omit Leading Text value is limited to 32 characters.
   
   
   For the ENTRY_DAT1 variable, the Omit Leading Text is set to AND ‘ (including the single quote) and the Omit Trailing Text is set to a single quote (‘).
   
   
3. Select File\Display Results, or press the Display Results button on the toolbar.
   
4. In the Prompt window, notice the date prompts default to *OMIT (this is the same as *ALL). To run as is, press the OK button, but if you want to see the effect of the *OMIT with the leading and trailing values, use the drop-down calendar to select a date for the second date like so (don’t press OK):
   
   
   This is a handy feature (only available in design mode): Press the Show SQL button to open the Merged SQL Statement window (below).
   
   Notice how the first date is missing from the WHERE clause along with it’s leading and trailing values, and the second date has been replaced with a value. This is a useful way to see if the SQL syntax is correct before running or saving the view.
   
   
   
5. Press the Close button and uncheck the second date value to return it to *OMIT like so:
   
   
   Press the OK button to see results.
   
   
6. Once satisfied with the changes to the view select File\Save or press the Save button to save the view.