Syntax1 - using a static MDQ file
Use Syntax1 to execute a SELECT statement that retrieves a specific column value from a large table in a database using the value of another input, as opposed to having to define the entire table as an input card and using other functions such as LOOKUP, SEARCHDOWN, or SEARCHUP. For more information about these functions, see the Functions and Expressions documentation.
If column data from a table or database varies because it may be based upon a parameter file, use Syntax2. For more information, refer to Syntax2 - using dynamic adapter commands.
Syntax1 is a three-argument syntax, examples of which follow:
DBLOOKUP
(
SQL_statement,mdq_file,database_name[:adapter_commands])
DBQUERY
(
SQL_statement,mdq_file,database_name[:adapter_commands])Using this syntax, DBLOOKUP and DBQUERY use the following arguments.
| Argument | Explanation | Must Be |
|---|---|---|
| SQL_statement | single-text-expression | a valid SQL statement |
| mdq_file | single-filename | a string literal |
| database_name | single-database-name | a string literal |
| adapter_commands | adapter-commands | a valid adapter command(s) |
The following table describes these arguments in more detail.
- Argument
- Description
- SQL_statement
- SQL statement as a text string. It can be any valid SQL
statement permitted by your database management system and supported by your
database-specific driver.
In addition to a fixed SQL statement, this argument can be a concatenation of text literals and data objects, enabling the concatenation of data values into your SQL statement.
- mdq_file
- Name of a database/query file (MDQ) produced by the Database Interface Designer. It contains the definition
of the database against which the SQL SELECT statement is to be executed. If the
MDQ file is in a directory other than the directory of the map, the complete path
must be specified. Note: The MDQ file is accessed at map build time and is not needed at runtime.
- database_name
- Name of a database in the MDQ file as defined in the Database Interface Designer. Note: This name is case-sensitive and must exactly match the name as defined in the Database Interface Designer.
- adapter_commands
- Name of an adapter command or commands. This name is an optional
argument that appears after database_name, which allows you to specify database adapter commands
in component rules in the Type Designer and map rules in the Map Designer.Note: The rules are defined and compiled into your map at design time.Note: All adapter commands (for example,-CS) can be either uppercase or lowercase, but not mixed case.