SQL Statements

You need to specify a SQL string for Data Retrieval and Key Retrieval. This string is part of a SQL statement. The content of the string should obey the SQL language rules. Which SQL dialect is used depends on the ODBC data source that you want to connect to. Each ODBC driver can implement its own SQL 'dialect'.

There are two deviations from the standard:

1. You should omit the SELECT part. ITP will generate the SELECT part based on the ITP Field and parameter definitions in the Entry.
2. The ?-symbol is used to denote parameters in ODBC SQL statements. These should be replaced by :x, where x is the sequence number of the Formal Parameter of the Entry.

ITP does not check the syntax of the SQL statement. When ITP executes a Model, it sends the SQL statement to the ODBC driver. The ODBC driver will check the syntax of the SQL statement.

Generation of the SELECT part for data retrieval

ITP generates a list of columns consisting of all the Fields of the Entry. If you specified the DATABASE_FIELD attribute for an ITP Field then ITP will use this. Otherwise, ITP will use the ITP Field name.

Generation of the SELECT part for key retrieval

Conform the Key Retrieval methodology, a list of columns is generated consisting of the formal parameters, again the formal parameters and -optionally- Screen Fields. If you specified the DATABASE_FIELD attribute for a formal parameter or a Screen Field then ITP will use this. Otherwise, ITP will use the formal parameter name (for parameters) or the ITP Field name (for Screen Fields).