selectdb

Calculate aggregate values for specified fields in the database. 

selectdb(LiteralFieldName)|(Function(LiteralFieldName)) %\
 {,(LiteralFieldNameN )|(Function(LiteralFieldName N ))} %\
   {fromEntity}{usingIndex}  %\
     { {u_where (SelectionCriteria) }|{u_condition(Condition) } } %\to (Destination{, DestinationN})

Since a selectdb statement can be quite complex, using the line continuation marker (%\) can greatly improve the readability of the statement.

Example:
selectdb (max(INVOICE_NUM), sum(AMOUNT)) %\
   from "INVOICES" u_where (CUSTOMER = "ABC") %\
   to (vInvoiceNum, TOTAL_DUM)

Clauses

Clauses
Qualifier Description
from Selects occurrences from a specified Entity
using Specifies the index Uniface should use for record-level DBMSs; ignored for field-level DBMSs. See the appropriate DBMS connector documentation to verify that the using clause is available for your DBMS.
u_where Specifies DBMS-independent selection criteria to identify the occurrences to use. For more information, see u_where.

It allows you to perform calculations on data in all occurrences that match the selection criteria, even if these have not yet become active in the component.

u_condition Specifies a DBMS-independent retrieve profile for selection. For more information, see u_condition.
to Specifies the destinations of the requested values.

Tip: A selectdb statement can be quite complex, so using the line continuation marker (%\) can greatly improve the readability of the statement.

Parameters

Parameters

Parameter

Data Type

Description

LitFieldName

Literal

Name of database field.

If used alone (without Function), Uniface transports the value of the specified field from one of the selected records. (This is usually the last record, but see the appropriate connector documentation for information about your DBMS.)

Function

String

Supported aggregation function. For more information, see Functions.

Entity

String

Entity whose occurrences are to be compared; if omitted, the active occurrence of the current entity (available in $entname) is used.

Index

Number

Index number required.

SelectionCriteria

String

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.

Condition

String

Conditional statement used as a retrieve profile. Can contain any legal conditional ProcScript expression, but each operand in the expression cannot contain yet another expression.

Destination

String

Literal name of a field or variable in the component. There must be one destination for each requested value (defined by Function and LitFieldName).

Return Values

Values returned in $status

Value

Meaning

>=0

For a record-level DBMS, the number of occurrences that matched the selection criteria.

For a field-level DBMS, the value is always 1, indicating that 0 or more occurrences matched.

-1

A LitFieldName does not exist, or Function cannot be used with this field type.

-3

Exceptional I/O error (hardware or software).

-4

Open request for table or file failed. The Entity is not in the component, or the table or file does not exist.

-9

An attempt to open a DBMS failed because the maximum number of DBMS logons has already been reached.

-15

Uniface network error.

-16

Network error: unknown.

-20

Nonexistent field in a u_where clause.

Values commonly returned by $procerror following selectdb
Value Error constant Meaning
-2 through -12 <UIOSERR_*> Errors during database I/O.
-16 through -30) <UNETERR_*> Errors during network I/O.
-1 <UGENERR_ERROR> An error occurred. A LitFieldName does not exist, or Function cannot be used with this field type.
-403 <UMISERR_UWHERE> Nonexistent field in a u_where clause.
-404 <UMISERR_TRX> The TRX-formatted DML statement from a where clause or an sql statement exceeds 16 KB.

Use

Allowed in all component types.

Description

The selectdb statement calculates the requested values from the database and transports them to the destinations specified. Each Function can be applied to a field in multiple occurrences in the database to produce the calculated value.

Note:  The selectdb statement does not work with variable-length fields.

If the from clause is supplied, Entity should identify the entity whose occurrences are to be compared. If Entity is an Up entity, the u_where clause is considered only if the foreign key is complete and not NULL. The clause can be used to further restrict the number of occurrences read for the Up entity.

In the to clause, there should be one Destination specified for each requested value. If only one requested value and Destination are specified, the parentheses are not required. (For the precise usage, see the examples.) If no data is retrieved, the destinations remain unchanged.

Guidelines and Limitations

In general, use the selectdb statement when very specific values need to be loaded from the database at run time; for example, to generate the next unique number, or for loading sums, averages, and so on, into dummy fields when building a report.

The selectdb statement is not very fast with databases that do not support this type of aggregate function. This is always the case with a record-level DBMS, because the driver and Uniface have to perform the relevant sorting and aggregate function.

Using selectdb in combination with a u_where clause with record-level databases can result in poor performance, because it is not possible to use the criteria to specify a preferred index (this is a general drawback of the u_where clause in record-level environments). Instead, the primary key index is used. If this is not the field to which you are referring, expect poor performance.

Be careful when using the selectdb statement in the read trigger immediately after a read statement, for the following reasons:

  • When selectdb is used in combination with a u_where clause that references one of the fields of the occurrence just read, the attempted selection fails because the occurrence has not been fetched yet.
  • When one of the fields of the occurrence just read is used as the Destination, the selection fails because the destination field is overwritten at the subsequent fetch. In the following example the value of PAYDATE is overwritten at the subsequent fetch: 
    ; trigger: Read
read u_where (PAYDATE = "") order by "INVOICE_AMOUNT"
selectdb (PRIMARY_KEY) from "INVOICES" to PAYDATE

Functions

The functions available with selectdb, along with the allowed data types for each, are shown in the following table. If the specified function cannot be used with the given data type, Uniface returns an error in $status. Depending on the packing code and target DBMS, there can be further restrictions on the use of these functions. For more information, see the appropriate connector documentation for your DBMS.

Functions and Data Types for selectdb
Function Result String Raw Numeric Float Date Time Datetime Boolean
ave Sum of all values in the named field in the database divided by count. No No Yes Yes No No No No
count Number of fields in the database that are filled. Yes Yes Yes Yes Yes Yes Yes Yes
max Largest value in the named field in the database (NULL is ignored). No No Yes Yes Yes 1 Yes 1 Yes Yes
min Smallest value in the named field in the database (NULL is ignored). No No Yes Yes Yes 1 Yes 1 Yes Yes
sum Sum of all values in the named field in the database. No No Yes Yes No No No No
Note:

1 The value returned to Destination has data type Datetime. If the Destination has data type $, the Datetime data remains. Otherwise, the value is converted to the destination data type.

Calculations Based on Selection Criteria

In the following example, the selectdb statement retrieves the highest invoice number and computes the total invoice amount from the INVOICE entity for the customer named in $1. The invoice number is loaded into $2 and the total invoice amount is placed in the dummy field TOTAL_DUM. 

trigger getFocus ; of entity INVOICE
  selectdb (max(INV_NUM), sum(AMOUNT)) %\
    from "INVOICES" %\
      u_where (CUSTOMER = $1) %\
    to ($2, TOTAL_DUM)
end

Generating Sequence Numbers 

trigger create
  selectdb max(INVOICE_NUMBER) %\
    from "INVOICES" %\
    to $1
    INVOICE_NUMBER = $1 + 1
end

Calculations Using Selected Data

The following example uses selectdb to produce a report giving a total for a certain period: 

trigger print
  SELECTDB sum(INV_AMOUNT) %\
    from "INVOICES" %\
      u_where (INV_DATE > $date(31-dec-1989)) %\
      & (INV_DATE < $date(01-apr-1990)) %\
    to Q1TOTAL.DUMMY
  print "SALESLASER", "A"
end

Counting Selected Occurrences

In the following example, the selectdb statement returns a count of the number of occurrences in ENT2 that have the same name as the occurrence of ENT1 that was just read.

Note:  It is necessary to qualify the field name left of the relational operator = in the u_where clause to force it to refer to ENT2, rather than the current entity, ENT1. 

trigger read ; of ENT1
  read
  if ($status = 0)
    selectdb count(NAME) from "ENT2" %\
    u_where (NAME.ENT2 = NAME.ENT1) %\
    to DUMMYFIELD
  endif
end

Related Topics