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:
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).
SELECT
CUST_ID,
CUSTNAME,
STATE,
ENTRY_DATE
FROM
SCSAMPLE90.CUSTOMERS CUSTOMERS
WHERE
STATE IN( &STATE )
AND ENTRY_DATE BETWEEN &ENTRY_DATE AND &ENTRY_DATE
ORDER BY
1
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.
WHERE 1 = 1 AND STATE IN(&STATE) AND ENTRY_DATE BETWEEN ‘&&ENTRY_DATE’ AND ‘&&ENTRY_DAT1’
WHERE 1 = 1 AND STATE IN(&STATE) AND ENTRY_DATE BETWEEN ‘’ AND ‘’
AND ENTRY_DATE BETWEEN ‘
(including the single quote) and the Omit Trailing Text is set to a single quote (‘
). AND ‘
(including the single quote) and the Omit Trailing Text is set to a single quote (‘
).
Still have questions? We can help. Submit a case to Technical Support.