Using SQL Views

Logging in

To use SQL Views, log in as inquiry schema user in the MSSQL Query Analyser.

Normal Inquiry

In this example, a Profile CUSTPRO has an alphanumeric Profile Ordinate CUSTNAME. To access all records with the value SMITH for CUSTNAME enter:

select * from  custpro_v
where custname =  'SMITH' ;

Note: ORDER BY clauses is not necessary to achieve ordering in Profile Ordinate or Class Ordinate order, and may in fact degrade performance if used.

This is equivalent to the following logic command:

Determine Every CUST.CUSTPRO (  "SMITH"  )

Inquiries over Events

Views over Events do not use a predefined index. If views over Events are used frequently or the Event Set population is large, you may get improved performance by creating an index over Event Set. For example take the Event Set named “event”:

create index fispec over event (ispec);

If you usually sort in a particular order, add more keys after the key Ispec. For example:

create index fispec over event (ispec, invoicenum);

Note: If the Event Set table in the database is reorganized, this index may be dropped. You should ensure that the index is present following changes to persistent Event Built-in Attributes with persistence set to true, changes to Event Profiles, or garbage collections of the Event Set table.

Inquiries Using Descending Numeric Key

In this example, a Profile CUSTDESC has a numeric Profile Ordinate CUSTNUM. The Profile Ordinate is stored in descending order. To access all records from the value 47 for CUSTNUM, enter:

select *  from custdesc_v where custnum  <= 47;

The Profile Ordinate, CUSTNUM, is used in the WHERE clause, and the index is defined as descending on the CUSTNUM column to get the correct ordering.

This SQL command is equivalent to the logic command:

Determine From CUST.CUSTDESC ( 47 )

Inquiries Using Descending Alphanumeric Key

In this example, a Profile CUSTINV has an alphanumeric Profile Ordinate CUSTNAME. Values of the Profile Ordinate are stored in descending order.

To access all records from the value JONES for CUSTNUM, key in the following:

select * from custinv_v where custname <= 'JONES'

This SQL command is equivalent to the logic command:

Determine From CUST. CUSTINV ( "JONES" )

In the SQL command:

The Data Item name used is custname.

To find records where CUSTNAME is blank, key in the following:

select *  from database_custinv_v where custname is null;

where, database is the database name in the system specification.

This SQL command is equivalent to the logic command:

DETERMINE; EVERY CUSTINV (GLB.SPACES)