QUERY BY SQL

version 11

QUERY BY SQL ({aTable; }sqlFormula)

The QUERY BY SQL command can be used to take advantage of the SQL kernel integrated into 4D. It can execute a simple SELECT query that can be written as follows:

   SELECT *
      FROM table
      WHERE <sqlFormula>

aTable is the name of the table passed in the first parameter and sqlFormula is the query string passed in the second parameter.

For example, the following statement:

   ([Employees];"name='smith'")

is equivalent to the following SQL query:

   SELECT * FROM Employees WHERE "name='smith'"

The QUERY BY SQL command is similar to the QUERY BY FORMULA command. It looks for records in the specified table. It changes the current selection of aTable for the current process and makes the first record of the new selection the current record.

Note: The QUERY BY SQL command cannot be used in the context of an external SQL connection; it connects directly to the integrated SQL engine of 4D.

QUERY BY SQL applies sqlFormula to each record in the table selection. sqlFormula is a Boolean expression that must return True or False. As you may know, in the SQL standard, a search condition can yield a True, False or NULL result. All the records (rows) where the search condition returns True are included in the new current selection.

The sqlFormula expression may be simple, such as comparing a field (column) to a value; or it may be complex, such as performing a calculation. Like QUERY BY FORMULA, QUERY BY SQL is able to evaluate information in related tables (see example 4). sqlFormula must be a valid SQL statement that is compliant with the SQL-2 standard and with respect to the limitations of the current SQL implementation of 4D. For more information about SQL support in 4D, please refer to the 4D SQL Reference manuel.

The sqlFormula parameter can use references to 4D expressions. The syntax to use is the same as for the integrated SQL commands or the code included between the Begin SQL/End SQL tags, i.e.: <<MyVar>> or :MyVar.

For more information, refer to the SQL Commands section.

Note: This command is compatible with the SET QUERY LIMIT and SET QUERY DESTINATION commands.

QUERY BY SQL does not use relations between tables defined in the 4D Structure editor. If you want to make use of related data, you will have to add a JOIN to the query. For example, assuming we have the following structure with a Many-to-One relation from[Persons]City to [Cities]Name:

   [People]
      Name
      City
   [Cities]
      Name
      Population

Using the QUERY BY FORMULA command, you can write:

   QUERY BY FORMULA([People];[Cities]Population>1000)

Using QUERY BY SQL, you must write the following statement, regardless of whether the relation exists:

   QUERY BY SQL([People];"people.city=cities.name AND cities.population>1000")

Note: QUERY BY SQL handles One-to-Many and Many-to-Many relations differently than QUERY BY FORMULA.

1. This example shows the offices where sales exceed 100. The SQL query is:

   SELECT *
      FROM Offices
      WHERE Sales > 100

When using the QUERY BY SQL command:

   C_STRING(30;$queryFormula)
   $queryFormula:="Sales > 100"
   QUERY BY SQL([Offices];$queryFormula)

2. This example shows the orders that fall into the 3000 to 4000 range. The SQL query is:

   SELECT *
      FROM Orders
      WHERE Amount BETWEEN 3000 AND 4000

When using the QUERY BY SQL command:

   C_STRING(40;$queryFormula)
   $queryFormula:="Amount BETWEEN 3000 AND 4000"
   QUERY BY SQL([Orders];$queryFormula)

3. This example shows how to get the query result ordered by a specific criterion. The SQL query is:

   SELECT *
      FROM People
      WHERE City ='Paris'
         ORDER BY Name

When using the QUERY BY SQL command:

   C_STRING(40;$queryFormula)
   $queryFormula:="City= 'Paris' ORDER BY Name"
   QUERY BY SQL([People];$queryFormula)

4. This example shows a query using related tables in 4D. In SQL you should use a JOIN to simulate this relation. Assuming we have the two following tables:

   [Invoices] with the following columns (fields):
      ID_Inv: Longint
      Date_Inv: Date
      Amount: Real
   [Lines_Invoices] with the following columns (fields):
      ID_Line: Longint
      ID_Inv: Longint
      Code: Alpha (10)

There is a Many-to-One relation from [Lines_Invoices]ID_Inv to [Invoices]ID_Inv.

Using the QUERY BY FORMULA command, you could write:

   QUERY BY FORMULA([Lines_Invoices];([Lines_Invoices]Code="FX-200") & (Month of([Invoices]Date_Inv)=4))

The SQL query is:

   SELECT ID_Line
      FROM Lines_Invoices, Invoices
      WHERE Lines_Invoices.ID_Inv=Invoices.ID_Inv
         AND Lines_Invoices.Code='FX-200'
         AND MONTH(Invoices.Date_Inv) = 4

When using the QUERY BY SQL command:

   C_STRING(40;$queryFormula)
   $queryFormula:="Lines_Invoices.ID_Inv=Invoices.ID_InvAND Lines_Invoices.Code='FX-200' AND MONTH(Invoices.Date_Inv)=4"
   QUERY BY SQL([Lines_Invoices];$queryFormula)

QUERY BY FORMULA.

If the format of the search condition is correct, the system variable OK is set to 1. Otherwise, it is set to 0, the result of the command is an empty selection and an error is returned. This error can be intercepted by a method installed using the ON ERR CALL command.