AMT Help Files

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

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

Contents

arrow_back Previous Next arrow_forward