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
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
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
Value |
Meaning |
---|---|
>= |
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. |
|
A LitFieldName does not exist, or Function cannot be used with this field type. |
|
Exceptional I/O error (hardware or software). |
|
Open request for table or file failed. The Entity is not in the component, or the table or file does not exist. |
|
An attempt to open a DBMS failed because the maximum number of DBMS logons has already been reached. |
|
Uniface network error. |
|
Network error: unknown. |
|
Nonexistent field in a u_where clause. |
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.
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