Specifying Expressions
You include expressions in visual queries as "fragments" that Vision includes in the code it generates for a frame. Specify these code fragments according to the following rules:
• The data type of the expression must be compatible with the data type of the column.
For example, do not use an integer value for an assignment or query restriction in a character column.
• Enclose character strings in single quotes.
• When assigning a value to a field on an Append frame or specifying a query restriction on a Browse or Update frame, do not include the name of the field, because Vision already knows the field name.
For example, to restrict retrievals on a Browse frame to customer accounts with a balance of more than $1,000, enter the following as a restriction for the Balance column on the visual query window:
>1000
not:
balance > 1000
• Use a colon (:) to indicate a reference to another field name.
For example, to retrieve customer records on a Browse frame where the amount of the current order is greater than the account balance, specify the following as a query restriction for the order_total column:
>:balance
(You do not need to include a colon with a field name when you specify a parameter.)
• You can refer to either displayed or hidden fields in an expression.
• The "=" is optional in expressing equivalence.
For example, to set the value of the Dept field to 'sales', you can enter either:
'sales'
or
= 'sales'
• Use LIKE and NOT LIKE to retrieve character strings.
For example, to retrieve employees whose last name begins with 'S,' enter the query restriction on the Browse or Update frame for the Lastname column as:
LIKE 'S%'
(The "%" matches any string of characters.)
• Use IN and NOT IN (or OR) to retrieve numeric values.
For example, to retrieve only salaries equal to $10,000 or $50,000 on a Browse frame, enter:
IN (10000,50000)
or
10000 OR 50000
in the Query Restriction field for the Salary column on the visual query window.
For detailed information on using expressions and for more information about pattern matching and comparison operators, see other sections of this guide.
How You Can Use the dbmsinfo Function
You can use the dbmsinfo function to retrieve various types of information from the database, such as current user names or transaction states. You only can retrieve information into the non-displayed fields on a frame.
The dbmsinfo function has the format:
dbmsinfo ('request_name')
where request_name represents a specific item of information. See the SQL Reference Guide for all possible values of request_name.
You can use the dbmsinfo function as an expression on Append, Browse, and Update frames. For example, you could assign the following value to a non-displayed field on an Append frame:
dbmsinfo ('username')
When a user appends a record, Vision enters the user's name into the database column corresponding to the non-displayed field.
Last modified date: 11/28/2023