Tech Tip: New behavior in QUERY BY FORMULA and QUERY SELECTION BY FORMULA commands

PRODUCT: 4D | VERSION: 11.2 | PLATFORM: Mac & Win

Published On: December 18, 2008

It is not necessary for a structural automatic relation to exist between table A and table B in order to use a query formula containing [Table_A]field_X = [Table_B]field_Y. As documented in the Addendum for 4D v11 SQL Release 2 and in the Language Reference for 4D v11 SQL Release 3, these commands carry out joins based on the SQL joins model. This mechanism can be used to modify the selection of a table according to a query carried out on another table without these tables being connected by an automatic relation (necessary condition in previous versions of 4D). See https://www.4d.com/docs/CMU/CMU00048.HTM.

In databases created starting with version 11.2 of 4D v11 SQL, this mechanism is activated by default. However, in converted databases, this mechanism is not activated by default for compatibility reasons but can be implemented via a preference.

Several new selectors (constants) are available for the SET DATABASE PARAMETER and GET DATABASE PARAMETER commands. Among them is selector 49 "QUERY BY FORMULA Joins"; Values: 0 (use database configuration), 1 (always use automatic relations) or 2 (use SQL joins if possible). See https://www.4d.com/docs/CMU/CMU00642.HTM.

The example below demonstrates these changes in action.

$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

Commented by Chris Norman on November 11, 2009 at 12:49 PM

If I write essentially this same query (also the same example in the language docs), I get a syntax error: operation is not compatible with the two arguments.

If I wrap the two query arguments in parentheses, it executes but returns zero records where I know I have several.

I have tested this several ways, with several fields and values, and can only conclude it does not work as documented.