The Basic DQL Vocabulary

DQL keywords and symbols are organized into nine categories:

· Commands

· Operators

· Functions

· Database Objects

· Relationships

· Symbols

· Values

· Variables and Constants

· Comments

Commands

Commands let you list, enter, modify, or delete records, execute another procedure or external program, and direct output to the screen, disk, or printer. DQL contains four types of commands:

• Processing Commands let you directly manipulate data. Examples include list records, modify records, and delete records.

• Procedural Commands let you control the flow of actions within a DQL Procedure. Examples include if...then...else, case...value...others, while...do, define, and message.

• Control Commands let you link any number of DQL Procedures together, making it possible to perform several actions on multiple data sources within a single procedure. Examples include run procedure, call menu, call program, import, and backup db.

• SQL Environment Commands let you view and process data stored in a remote SQL database. Examples include exec SQL, commit, rollback, tran on, and tran off.

Operators

Operators are used to manipulate variables and tell DataEase how to carry out certain Processing and Control Commands (such as the order in which to process records, what statistics to generate, etc.). DQL includes seven types of Operators:

• Comparison Operators are used to compare two values. Examples include <= (less than or equal to), > (greater than), and = (equal to).

• Grouping/Sorting Operators tell DataEase how to group and order records when displaying or printing data. Examples include in groups, in order, and in reverse.

• Relational Operators all and any are used to list records related to the record currently being processed.

• Statistical Operators summarize the values of a field for all records processed. Examples include sum, mean, max, and min.

• Conditional Statistical Operators generate summary information about a set of records that meet a specified condition. Examples include count, percent, and item.

• Relational Statistical Operators summarize information about fields in a set of records related to the record currently being processed. Examples include count of, highest of, lowest of, mean of, and sum of.

• General Operators, include the arithmetic operators (*(asterisk) ,/ , + (addition) , and - (subtraction) ), the assignment operator :=), and the logical operators (and and or ).

Functions

A function is a routine that performs a particular calculation, text manipulation, or other data processing task. DQL provides 58 functions grouped into nine categories: Date, Time, Spell, Text, if, Math, Financial, Scientific, and Trigonometric.

A function is usually followed by one or more parameters (also called arguments) enclosed within parentheses. A parameter is information you supply to specify the operation of the function.

For example, the firstc function lets you extract a number of characters from the beginning of a text value. To use the firstc function, you must supply information telling DataEase: (1 ) from what text value to extract characters, and (2) how many characters to extract. The statement below shows the firstc function and the two parameters needed to extract the first four characters in a LAST NAME field:

 firstc (LAST NAME,4)

When processing a MEMBERS record for a member named Williams, the above statement returns the value, "Will".

DataEase lets you use functions in field Derivation Formulas as well as in a DQL Procedure.

The Functions pick list in the Script Editor is illustrated below:

Database Objects

Database objects are the entities you create in an application to store and access data. Tables, columns, fields, relationships, and documents are all examples of database objects that can be specified in a DQL script.

For example, the script below specifies two table names and three column names that tell DataEase what data to print in a report:

 for MEMBERS ;

  list records

  FIRST NAME ;

  LAST NAME ;

  all FAMILY MEMBERS FIRST NAME .

 end

Relationships

Relationships link records stored in different database tables. In a DQL Procedure, you can access data in a related table using predefined relationships (stored in the Relationships form) and adhoc relationships that are defined within the procedure.

The figure below shows the Relationships form.

Symbols

See DQL 8 for more information on specific DQL language elements.

A symbol is a character used to punctuate a DQL script. Examples include a period, comma, semicolon, and parenthesis.

Additionally, special symbols called wild card symbols are used to represent unknown characters. Wild card symbols include the asterisk (*), tilde (#), and question mark (?). The example below shows a question mark used to take the place of an unknown character:

for MEMBERS with LAST NAME = "Anders?n " ;

list records

 LAST NAME;

 FIRST NAME.

end

Variables

A variable is a value that can change while a script is being processed. A variable may contain a number, numeric string, text string, time, or date value.

When you define a variable, you specify a word or letter to represent the variable in the script. You also specify what type of data is to be stored in the variable. The following line defines a variable named DISCOUNT, used to store a number value:

define temp "DISCOUNT" Number.

Once a variable is defined, you can use the assign command to set its value. The following line sets the value of the DISCOUNT variable to fifteen percent of a member's TOTAL DUE:

assign temp DISCOUNT := 0.15 * TOTAL DUE.

Although a variable can hold only one value at a time, that value can change any number of times during processing. In the example above, the value of DISCOUNT might change with each MEMBERS record processed.

DataEase lets you define a temporary variable to store a value during a single DQL Procedure, or a global variable used to pass a value from one DQL Procedure to another.

Values

Values are the data processed by a procedure. A value can be any number or text string stored as a constant or variable, or the contents of a data field (the current value in the CLUB NAME field, for example).

Comments

Comments are notes and explanations you can include in a DQL script to make it easy for you or another programmer to understand. Indicate the beginning of a comment with a double hyphen (-). When executing the script, DataEase ignores all text between the double hyphen and the end of the line.

 -- This script lists MEMBERS from Texas.

 for MEMBERS with STATE = "TX" ; -- select Texas members.

 list records    -- Print the members'
  FIRST NAME;    -- first and last names.
  LAST NAME.
 end      -- end of script.