SQL Syntax

In USoft Developer, key application behavior is specified by using SQL in constraint definitions, underlying query definitions, Logical View definitions and many other contexts.

To fine-tune application behavior, you can use SQL to retrieve data into the Windows GUI interface, manipulate data from the Windows GUI interface, or validate data input in the Windows GUI interface. In addition, you use SQL for miscellaneous purposes, such as calling SQL's ROUND() function to round a number to the nearest integer.

You can use any SQL syntax supported by your database vendor. To keep your USoft application portable between databases, however, you should adhere to standards. This is particularly a concern when calling SQL functions. USoft converts certain key SQL functions automatically between databases. Others are database-specific. Please consult the USoft Definer Help for details.

From a syntax point of view, USoft Developer lets you use several SQL constructs:

•Regular SQL statements, such as full SELECT or UPDATE statements.

For example, you decide what data is shown in an info window by editing its underlying query (a SELECT statement).

•Stand-alone SQL conditions. The following Decision SQL does not retrieve data to pass to Yes Action scripts, but merely performs a test using SQL WHERE syntax:

:PRICE >= 10000

The :PRICE construct is an embedded method call reading a value from the screen.

•SQL expressions. The USoft native FORMULA() method can be used to evaluate SQL expressions outside SQL context.

•Stand-alone SELECT clauses. This syntax is supported for backward compatibility only. The following instruction takes an on-screen value from field_1 and pastes it into field_2:

SqlScript(SELECT :"query.field_1()" field_2)

Both the fact that the SELECT clause is stand-alone (has no FROM clause), and the use of the column label to specify the control for output are obsolete. Write a method call instead:

query.field_1(query.field_2))