SQL - Structured Query Language

SQL, or Structured Query Language, is used to specify (query) which information must be selected and printed from the Amadeus - PMPRO database. The statements used in the report definitions in Amadeus - PMPRO must conform to the Microsoft FoxPro SQL standards.

Steps to change the SQL

You must have knowledge of the Amadeus - PMPRO database structure before you can define or change the SQL. Incorrect changes will cause the report not to work.

1. Select Define Reports from the Setup » Reports menu.

2. Select the report group in which the report you would like to change is located and choose [OK].

3. Select the report you would like to change and choose [SQL].

4. Notepad will open a file with the name SqlState.Txt. This file contains the SQL of the report you have selected.

5. Make the required changes. Close notepad and save the changes.

6. You automatically return back to the Amadeus - PMPRO system, press any key and choose [OK] to save the SQL in Brilliant.

More information

The general format of an SQL statement includes many parts, a very simplified and brief explanation of the most used SQL clauses are given below.

• Syntax

  SELECT [ALL | DISTINCT]
  [<alias>.] <select_item> [AS <column_name>]
  [,[<alias>.] <select_item> [AS <column_name>] ...]

  FROM <table> [<local_alias>] [,<table>[<local_alias>] ...]

  [WHERE <joincondition> [AND <joincondition> ...]
  [AND | OR <filtercond> [AND | OR <filtercond> ...]]]

  [GROUP BY <groupcolumn> [, <groupcolumn> ...]]

  [UNION [ALL] <SELECT command>]

  [ORDER BY <order item> [ASC | DESC]
  [, <order item> [ASC | DESC] ...]]

   

• The SELECT clause

  SELECT is a SQL command you use to retrieve data from one or more table/.DBF. The SELECT clause specifies the fields, constants and expressions that will be displayed in the query results. Each item you specify with <select_item> generates one column of the query results. If more than one item has the same name, be sure to qualify the matching names by including the table/.DBF alias and a period before the item name. By default, all of the rows (ALL) in the query results are displayed. Include DISTINCT to exclude duplicates of any rows from the query results.

   

• The FROM clause

  The FROM clause lists the tables/.DBF's that contain the data to be retrieved by the query. <Local alias> is a temporary name for a table/.DBF specified with <table>. If you specify a local alias, you must use the local alias in place of the table/.DBF name throughout SELECT. The local alias doesn't effect the Amadeus - PMPRO environment.

   

• The WHERE clause

  The WHERE clause is required to retrieve data from multiple tables. It tells Amadeus - PMPRO to include only certain records in the query results. <joincondition> specifies fields that link the tables in the FROM clause. If you include more than one table/.DBF in a query, you should specify a join condition for every table/.DBF after the first. If you include two tables in a query and don't specify a join condition, every record in the first table/.DBF is joined with every record in the second table/.DBF as long as the filter conditions are met. Such a query can produce lengthy results. Multiple join conditions must be connected with the AND operator. Each join condition has the following form: <field1><comparison><field2>.

   

  <field1> is the name of a field from one table, <field2> is the name of the field from another table

  <comparison> is one of the following operators: =, ==, !=, #, <>, <, <=, >, >=

   

• The GROUP BY clause

  Including the GROUP BY clause groups rows in the query based on values in one or more columns. <groupcolumn> can be the name of a regular table/.DBF field, or a field that includes an SQL field function, or a numeric expression indicating the location of the column in the result table/.DBF (the leftmost column is number 1).

   

• The UNION ALL clause

  Include the UNION clause to combine the final results of one SELECT with the final results of another SELECT. By default, UNION checks the combined results and eliminates duplicate rows. Use the optional ALL key word to prevent UNION from eliminating duplicate rows from the combined results. You can use parentheses to combine multiple UNION clauses. UNION clauses follow these rules:

  • You cannot use UNION to combine sub queries.

  • Both SELECT's must output the same number of columns.

  • Each column in the query results of one SELECT must have the same data type and width as the corresponding column in another SELECT.

  • Only the final SELECT can have an ORDER BY clause, which must refer to output columns by number. If an ORDER BY clause is included, it affects the entire result.

• The ORDER BY clause

  ORDER BY sorts the query results based on the data in one or more columns. Each order item must correspond to a column in the query results and can be one of the following:

  • A field in a FROM table that is also a select item in the main SELECT clause (not in a sub query).

  • A numeric expression indicating the location of the column in the result table. (The leftmost column is number 1).

  You can specify the optional DESC key word if you want your query results to appear in descending order according to the order item or items. ASC specifies ascending order for the query results and is the default for ORDER BY. Query results appear unordered if you don't specify an order with ORDER BY.