u_where

Provide a DBMS-independent profile for selection with read and selectdb.

u_where  (SelectionCriteria)

Example: read u_where (PAY_BY_DATE.INVOICE < PAIDDATE.INVOICE)

Parameters

SelectionCriteria—one or more relational phrases with the operands linked by relational operators (<, !=, and so on), connected by logical operators (&, |, and so on). The operand to the left of the operator must be a field in an entity; the operand to the right can be a value.

Return Values

For more information, see read and selectdb.

Use

Allowed in all component types.

Description

The u_where clause is used to specify DBMS-independent selection criteria for a read or selectdb statement. It is not an independent ProcScript statement. u_where cannot be used with u_condition.

Note:  The u_where and u_condition clauses are very similar. However, the u_condition clause is interpreted at run time, whereas u_where is interpreted at compilation.

The SelectionCriteria generated from the u_where clause are passed to the DBMS connector. This data is limited to 8192 bytes. A maximum of 45 retrieve profile characters can be entered, including those on the u_where clause and those entered in fields by the user; if the search profile of a single field exceeds 512 bytes, it is truncated to 512 bytes.

Selection Criteria

Possible profile characters are always stripped from the SelectionCriteria, with the exception of "*" and "?", which are treated as literals. For example: 

read u_where (NAME = "A|B")

results in the following SelectionCriteria: 

NAME = "AB"

In each relational phrase in the selection criteria, the operand to the left of the relational operator refers to a field in an entity. By default, it is to a field in the current entity. To specify another entity, qualify the field name with the entity name, for example, PAY_BY_DATE.INVOICE. If a value is used as the first operand, the criteria will not be evaluated correctly.

The operand to the right of the relational operator determines whether the relational phrase refers to values in the database or in the component. The rules for this are:

  • If the field following the operator is in the same entity and no substitution or format conversion is applied, Uniface uses the database value of that field when evaluating the statement.

    For example, PAY_BY_DATE and PAIDDATE are in the same entity, so the database value of PAIDDATE is used:

    ; Read trigger for entity INVOICE
; read all invoices from database which were paid after the PAY_BY_DATE
read u_where (PAY_BY_DATE.INVOICE < PAIDDATE.INVOICE)
  • If the field following the operator is not in the same entity, Uniface uses the component value of that field when evaluating the statement. The field must be present in the component.

    For example, PAY_BY_DATE and DATE are in different entities, so the value of DATE.HEADER in the component is used.

    ; Read trigger for entity INVOICE
; read all occurrences with PAY_BY_DATE earlier than the date in the header frame
read u_where (PAY_BY_DATE.INVOICE < DATE.HEADER)
  • If the field following the operator is preceded by a string substitution marker (%%) or a format conversion function (for example, $number or $date), Uniface uses the component value of that field when evaluating the statement.

    For example, the $date function is used to convert the data in the RPT_PERIOD field, so the value of the component field is used.

    ; Read trigger for entity INVOICE
; read all occurrences where the invoice date 
; is later than a specified date
read u_where (INVOICE_DATE.INVOICE > $date(RPT_PERIOD.NM)

Performance Considerations

Although u_where is DBMS-independent (that is, it works with every DBMS supported by Uniface), performance when using u_where can vary greatly. Performance depends both on the way you code the ProcScript module and on the DBMS you are using. This is because Uniface leaves the evaluation of the statement to the DBMS, where possible; if the DBMS cannot handle this, Uniface does it itself. Leaving it to the DBMS is almost always faster.

Generally, a record-level DBMS cannot perform this sort of selection. Instead, the connector and Uniface have to perform the relevant sorting, and, if used in conjunction with selectdb, the aggregate function. In a record-level environment, it is not possible for the DBMS driver to specify a preferred index for selecting records when using selectdb in combination with a u_where clause. Instead, the primary key index is used. If this is not the field you are referring to, you can expect poor performance.

Using u_where in read

The following example causes all occurrences of the relevant entities to be retrieved for which the following conditions are true:

  • NAME begins with 'A'.
  • SALARY is greater than or equal to 4975. 
read u_where ((name = "A*") & (salary >= 4975))

The following example shows how to use a global variable to supply a clause value to the read statement. The global variable is called $$NAME, the field being selected on is called FNAME: 

read u_where (FNAME = "%%$$name")

Using u_where in selectdb

The following example retrieves the average salary and the number of employees from the employee database for which the following conditions are true:

  • The employee’s salary is less than or equal to 4975.
  • The employee’s age is less than or equal to 25 (assuming current year is 2015).

The resulting average salary value is loaded into the field AVERAGE.DUMMY and the total number of occurrences is loaded into the field TOTAL.DUMMY. (Note the use of the continuation markers to improve legibility.) 

vAvgSalary = 4975
selectdb (ave(SALARY), count(NAME)) %\
   from EMPLOYEE %\
   u_where ((SALARY <= vAvgSalary) & (birthdate <= $date(01-01-1990)) %\
   to (AVERAGE.DUMMY, TOTAL.DUMMY)

Related Topics