4D v16.3
QUERY BY FORMULA
- 4D Language Reference
-
- Queries
-
- DESCRIBE QUERY EXECUTION
- Find in field
- Get last query path
- Get last query plan
- GET QUERY DESTINATION
- Get query limit
- ORDER BY
- ORDER BY FORMULA
- QUERY
- QUERY BY ATTRIBUTE
- QUERY BY EXAMPLE
- QUERY BY FORMULA
- QUERY SELECTION
- QUERY SELECTION BY ATTRIBUTENew
- QUERY SELECTION BY FORMULA
- QUERY SELECTION WITH ARRAY
- QUERY WITH ARRAY
- SET QUERY AND LOCK
- SET QUERY DESTINATION
- SET QUERY LIMIT
QUERY BY FORMULA
| QUERY BY FORMULA ( aTable {; queryFormula} ) | ||||||||
| Parameter | Type | Description | ||||||
| aTable | Table |
|
Table for which to return a selection of records | |||||
| queryFormula | Boolean |
|
Query formula | |||||
QUERY BY FORMULA looks for records in aTable. It changes the current selection of aTable for the current process and makes the first record of the new selection the current record.
QUERY BY FORMULA and QUERY SELECTION BY FORMULA work exactly the same way, except that QUERY BY FORMULA queries every record in the entire table and QUERY SELECTION BY FORMULA queries only the records in the current selection.
Both commands apply queryFormula to each record in the table or selection. The queryFormula is a Boolean expression that must evaluate to either TRUE or FALSE. If queryFormula evaluates as TRUE, the record is included in the new selection.
The queryFormula may be simple, perhaps comparing a field to a value; or it may be complex, perhaps performing a calculation or even evaluating information in a related table. The queryFormula can be a 4D function (command), or a function (method) or expression you have created. You can use wildcards (@) in queryFormula when working with Alpha or text fields as well as the "contains" (%) operator for keyword queries. For more information, please refer to the description of the QUERY command.
If queryFormula is omitted, 4D displays the query dialog box (the user can add a line of formula by Alt+clicking on the [+] button).
When the query is complete, the first record of the new selection is loaded from disk and made the current record.
These commands are optimized and can more particularly take advantage of indexes. When the type of query allows it, these commands execute queries equivalent to the QUERY command. For example, the statement QUERY BY FORMULA([mytable]; [mytable]myfield=value) will be executed just like QUERY([mytable]; [mytable]myfield=value), which will allow the use of indexes. 4D can also optimize queries containing parts that cannot be optimized, by first executing the optimized parts and then combining the results with the rest of the query. For example, the statement QUERY BY FORMULA[mytable];Length(myfield)=value) will not be optimized. On the other hand, QUERY BY FORMULA([mytable];Length(myfield)=value1 | myfield=value2) will be partially optimized.
These commands by default carry out "joins" like SQL when you compare fields from different tables. This means that it is not necessary for a structural automatic relation to exist between the tables. For example, you can execute a statement of the type QUERY BY FORMULA([Table_A];([Table_A]field_X = [Table_B]field_Y) & ([Table_B]field_Y = "abc")) (see example 3). The first part of the formula ([Table_A]field_X = [Table_B]field_Y) establishes the join between the two fields and the second part ([Table_B]field_Y = "abc") defines the search criteria (at least one criterion must be set).
If they exist, the relations between the tables are not used as a rule. However, these commands will use automatic relations in the following cases:
- If the formula cannot be broken down into elements of the { field ; comparator ; value} form
- If two fields of the same table are compared.
Note: For compatibility reasons, it is possible to deactivate the joins mechanism, either globally via the database Preference (converted databases only) or per process using the SET DATABASE PARAMETER command.
4D Server: This command is executed on the server, which optimizes its execution. Note that when variables are called directly in queryFormula, the query is calculated with the value of the variables on the client machine. For example, the statement QUERY BY FORMULA([mytable];[mytable]myfield=myvariable) will be executed on the server but with the contents of the client machine's myvariable.
Compatibility note: Until 4D Server v11, this command was executed on the client machine. For reverse compatibility, this behavior is maintained in converted databases. However, a compatibility property or a selector of the SET DATABASE PARAMETER command enables server-side execution in converted databases.
This example finds the records for all invoices that were entered in December of any year. It does this by applying the Month of function to each record. This query could not be performed any other way without creating a separate field for the month:
QUERY BY FORMULA([Invoice];Month of([Invoice]Entered)=12) ` Find the invoices entered in DecemberThis example finds records for all the people who have names with more than ten characters:
QUERY BY FORMULA([People];Length([People]Name)>10) ` Find names longer than ten charactersThis example activates SQL joins for a specific query by formula:
$currentVal:=Get database parameter(QUERY BY FORMULA Joins)
SET DATABASE PARAMETER(QUERY BY FORMULA Joins;2) `Activate SQL joins
`Query all the lines of "ACME" client invoices even though the tables are not related
QUERY BY FORMULA([invoice_line];([invoice_line]invoice_id=[invoice]id&[invoice]client="ACME"))
SET DATABASE PARAMETER(QUERY BY FORMULA Joins;$currentVal) `We re-establish the current settings
QUERY
QUERY BY SQL
QUERY SELECTION
QUERY SELECTION BY FORMULA
Product: 4D
Theme: Queries
Number:
48
Modified: 4D v11 SQL Release 2
4D Language Reference ( 4D v16)
4D Language Reference ( 4D v16.1)
4D Language Reference ( 4D v16.2)
4D Language Reference ( 4D v16.3)
