MS SQL Server data source schema objects include views. A view is a presentation of data in one or more tables. Views appear as tables, not as views, in the Data Dictionary's table list for the schema holder. In addition, the schema holder contains no unique index information for views. Because views do not have unique indexes, you cannot modify any of the data that a view contains; however, you can access a view with the FOR EACH, FIND NEXT, and GET NEXT OpenEdge statements. Furthermore, because views do not have index definitions, the DataServer cannot reposition the cursor to retrieve individual records. Thus, you must be sure to get all of the data that you need in a single database request.
The following ABL code example retrieves data from a view:
OPEN QUERY query-name FOR EACH view-name.
REPEAT:
GET NEXT query-name.
DISPLAY view-name.
. . .
END.
If a view has a unique combination of columns, you can simulate a unique index using the Data Dictionary. You can then access a view that has a simulated unique index just as you do a table; that is, you can scroll backward and forward, and update, create, and delete data. See Modifyingfield-level information for information on how to do this.
Some views are the results of joins and contain data from more than one table. You can also provide unique index information for these views if they have a unique combination of columns. You can then scroll backward and forward, but the MS SQL Server data source does not allow you to create or delete data in a multi-table view. You can, however, update data in some views.
The DataServer does not support access to columns in views that are the results of aggregates or computations unless the calculated column has a name associated with it. You assign a specific name to a calculated column when you define a data source view. For example, the following SQL statement names a computed column in a view definition:
create view view-name as select cc = count(cust_num) from customer
You can also access those views by using the RUN STORED-PROC send-sql-statement option to send a SQL statement to select the data from the view. In this case, you can access the view without adding index definitions for the view to the schema holder.
Although the schema holder contains your views, the Data Dictionary's SQL View Report does not list them, nor can you access them through the PRO/SQL menu functions.
