Database Views


Note: Before Views can be used in the code they have to be created in the database, see Database Views.


Note: Views are readonly! Updates, inserts or deletes are not possible.

Description

Views are a alternative way of reading data from tables in a database. Before a View can be used it has to be defined in the database. When used in the code the View returns a result set of table fields as set in the Database View. Table fields can be read across multiple tables in any way possible in the Database View code (see Database Views).

Syntax

In the code a View can be defined with the following syntax:

<Code View> : View ( [<Application>.]<Database View>)

Item	Description
<Code View>	A free to choose name to identify the view in the code.
<Application>	Name of the external application that contains the requested database view. In the ControlCenter, this application and corresponding database have to be specified. Omit this option if you do not refer to an external table in your query. To use this option, it is needed that the external application uses the same repository as the calling application. The generation options for both applications are expected to be set equally. Always the newest revision that is available will be used (disregarding the gensets).
<Database View>	The name to identify the Database View that must be used by this view.

Assignments

Since a View is only used to read information from the database only assignments from View fields to a variable are possible.

<Variable> := <Code View>.<FieldName>

Item	Description
<Variable>	The name of a variable to assign the value of the field to.
<Code View>	The name of the Code View as defined in the variables definitions.
<FieldName>	The name of the field to use the value of for the assignment.

Functions

View functions can be used as follows in the code:

<Code View>.<Function> ( [<Function parameters>] )

Item	Description
<Code View>	The name of the View to use as defined in the variables definitions.
<Function>	The name of the function to execute (see the list below).
<Function parameters>	Specification of the parameter values to be used by the function when required (see the function descriptions below).

Function Descriptions

Function	Parameters	Description
Clear	None	Clear all the values of the current record in memory and also clears any Where / OrderBy values previously set.
Close	None	Close the active View result set. Also clears the Where / OrderBy valuse previously set. Note: is called implicit by the EndLoop statement in a Loop on a view after the last record.
Next	None	Moves to the next record in the result set of the View.
Open	None	Opens the result set of the view.
OrderBy	Comma separated list of FieldNames	List of View FieldNames to sort the result set on. It must be placed before the Open View or Loop View command and after a defined Where command.
ResultOkTo	StatusField (Boolean or an Alpha 5)	Set the result status not to ResOk but to the specified variable <StatusField>.
Where	Logical list of Where conditions	Where condition as in a Table Query. It must be placed before an Open View or Loop View Command. See also WHERE clause in Queries.

Remarks

The View can either be opened with the <Code View>.Open () function or by using a Loop ... Endloop on the View, as with table and cursor queries.
When opened with the Open function the next record from the result set can be retrieved with the Next function. The end of the result set can be detected by checking the Boolean <Code View>.Eof propery. When using a Loop on the View, closing is done automatically after the last record in the result set.

Examples

Example 1

SQL Code of the view TESTVIEW in the database

SELECT CUST_NO AS KLANTNUMMER, FIRSTNAME AS VOORNAAM, LASTNAME AS ACHTERNAAM, STREET AS STRAAT,
CITY AS PLAATS, COUNTRY_CODE AS LAND
FROM [[T_CUSTOMER]]

Code in the Form or Report

Definition in Local Variables:

var
    persoon : view (testview)

Code:

    persoon.orderby (achternaam)
    loop persoon
        view_lay.klantnummer := persoon.klantnummer
        view_lay.voornaam    := persoon.voornaam
        view_lay.achternaam  := persoon.achternaam
        view_lay.straat      := persoon.straat
        view_lay.plaats      := persoon.plaats
        view_lay.land        := persoon.land
        print (view_lay)
        skip (1)
    endloop

Example 2

SQL Code of the view TESTJOINVIEW in the database

SELECT T_CUSTOMER.CUST_NO AS KLANTNUMMER, T_CUSTOMER.FIRSTNAME AS VOORNAAM,
T_CUSTOMER.LASTNAME AS ACHTERNAAM, T_CUSTOMER.STREET AS STRAAT,
T_CUSTOMER.CITY AS PLAATS, T_COUNTRIES.NAME AS LAND
FROM [[T_CUSTOMER]]
JOIN T_COUNTRIES ON T_CUSTOMER.COUNTRY_CODE=T_COUNTRIES.CODE

Code in the Form or Report

Definition in the Local Variables:

var
    persjd  : view (testjoinview)

Code:

    persjd.where (persjd.land = 'Netherlands')
    persjd.orderby (dsc voornaam, asc achternaam, dsc plaats)
    persjd.open ()
    loop while not persjd.eof
        view_lay.klantnummer := persjd.klantnummer
        view_lay.voornaam    := persjd.voornaam
        view_lay.achternaam  := persjd.achternaam
        view_lay.straat      := persjd.straat
        view_lay.plaats      := persjd.plaats
        view_lay.land        := persjd.land
        print (view_lay)
        skip (1)
        persjd.next ()
    endloop