Use the RENAME VIEW command.
●
For example, the following code renames product_view to products_all_view : RENAME VIEW product_view TO products_all_view
The database containing the view must be open before you can rename the view.
Deleting a View
You can delete a view definition from a database using the Project Manager or the DELETE VIEW command.
Before deleting the view, make sure the database containing the view is open and set as the current database.
To delete a view
In the Project Manager, select a database, and then the view name, and then choose Remove.
-or-●
Use the DELETE VIEW or DROP VIEW command.
●
For example, the following code deletes product_view and customer_view from the database:
DELETE VIEW product_view DROP VIEW customer_view
Note These commands have the same effect; DROP VIEW is the ANSI SQL standard syntax for deleting a SQL view.
Creating a Multitable View
To access related information that is stored in separate tables, you can create a view and add two or more tables, or you can modify an existing view by adding tables. To add the tables, you can use the View Designer or the CREATE SQL VIEW command. After adding the tables, you can expand your control of the view results using the join
condition you define between the tables.
To create a multitable view
In the Project Manager, create a view and add the tables you want in the View Designer.
-or-●
Open a database and use the CREATE SQL VIEW command, adding table names to the FROM clause and join conditions.
Just adding the tables to the CREATE SQL VIEW command produces a cross-product. You need to specify a join condition in either the FROM clause or the WHERE clause of the statement to match related records between the tables. If persistent relationships between the tables exist, they are automatically used as join conditions.
●
Defining and Modifying Join Conditions
Typically, to define a join condition, you use the relationships established on the primary and foreign key fields between the tables. For example, you might want to find information on the orders, including information on the customer who placed the order. You can create a view using the Customer and Orders tables. You specify a join condition to compare values in the fields they have in common and, usually, return those that are equal. In the example, Customer and Orders both have a Customer ID field.
To define join conditions in a view
In the Project Manager, create or modify a view, and then add the tables you want in the View Designer.
-or-●
Open a database and use the CREATE SQL VIEW command, adding table names to the FROM clause and join conditions to the FROM clause.
●
Inner joins specified in the View Designer and displayed in the SELECT - SQL statement
The following code creates the new view as described in the example above, using the FROM clause to specify the join conditions for the view:
OPEN DATABASE testdata
CREATE SQL VIEW cust_orders_view AS ; SELECT * FROM testdata!customer ; INNER JOIN testdata!orders ;
ON customer.cust_id = orders.cust_id
The join condition has several aspects: the type of join, the fields to join on, and the operator for comparing the fields. In this case, which is an inner join, only rows from the customer table that match one or more records in the orders table are included in the result.
To change the results of the view to meet your specific needs, you can specify:
Fields in the join
●
Comparison operators between the fields
●
A sequence of joins, if you have two tables in your view
●
The type of join
●
Specifying joins on fields other than the primary and foreign keys can be useful in specific instances, but are not used in most views.
By changing the comparison operator, you can control which records are compared and returned in a manner similar
to a filter. For example, if you are using a date field in the join, you can use the comparison operator to include only records before or after a certain date.
For more information about the sequence of joins, see Defining Multiple Join Conditions later in this chapter.
Choosing a different join type allows you to expand your query results to include both records that match the join condition and those that do not. If you have more than two tables in your view, you can change your results by changing the order of joins in the FROM clause.
You can modify the join types in your view using the View Designer or the language.
To modify a join type
Open a database and use the CREATE SQL VIEW command, adding table names and join conditions to the FROM clause.
●
Including Non-Matching Records in Results
If you want to include non-matching rows in your results, you can use an outer join. For example, you might want a list of all customers and whether or not they have placed an order. In addition, for customers that have placed orders, you might want the order numbers included in the view. When you use an outer join, the empty fields of the
non-matching rows return null values.
You can also use the language to create this view by using the following code:
OPEN DATABASE testdata
CREATE SQL VIEW cust_orders_view AS ; SELECT * FROM testdata!customer ; LEFT OUTER JOIN testdata!orders ; ON customer.cust_id = orders.cust_id
To control which non-matching records are included in your view, you can choose from the following join types.
To Use
Return only records from both tables that match the comparison
condition set between the two fields in the join condition. Inner join Return all records from the table to the left of the JOIN keyword
and only matching records from the table to the right of the keyword.
Left outer join
Return all records from the table to the right of the JOIN
keyword and only matching records from the table to the left of the keyword.
Right outer join
Return matching and non-matching records from both tables Full outer join
Defining Multiple Join Conditions
If you create views or queries with more than two tables, you can change the results by the order your join conditions are specified. For example, you might want to find information on the orders, including information on the employee who made the sale and the customer who placed the order. You can create a view using the customer, orders, and employee tables and specify inner join conditions on the fields they have in common: customer and orders both have a customer ID field; orders and employee both have an employee ID field.
This view has the following underlying SQL statement:
OPEN DATABASE testdata
CREATE SQL VIEW cust_orders_emp_view AS ; SELECT * FROM testdata!customer ;
INNER JOIN testdata!orders ;
ON customer.cust_id = orders.cust_id ; INNER JOIN testdata!employee ;
ON orders.emp_id = employee.emp_id Using Joins in the WHERE Clause
You can specify your join conditions in the WHERE clause; however, you cannot specify a join type as you can in joins in the FROM clause. For remote views, the join clause always appears in the WHERE clause.
The following code creates the same view as the previous example, using the WHERE clause to specify the join conditions for the view:
OPEN DATABASE testdata
CREATE SQL VIEW cust_orders_emp_view AS ; SELECT * FROM testdata!customer, ;
testdata!orders, testdata!employee ; WHERE customer.cust_id = orders.cust_id ; AND orders.emp_id = employee.emp_id