This dialog box appears when you choose the "Show
SQL Query" command from the Crystal Reports menu.
The SQL Query dialog box displays the SQL query
that the program is sending to your SQL server.
The query can have one or more of the following
parts:
SELECT
FROM
INNER JOIN
WHERE
ORDER
BY
SELECT
SELECT lists all of the fields used in the
report, including fields used in groups, formulas, totals, the sort order, the
selection formula, and ranges.
For example, the following shows that the report
is using the "CUSTOMER_NAME", "LAST_YEARS_SALES", "Order Date", and "Country"
fields:
SELECT
"Customer"."CUSTOMER_NAME", "Customer"."Last Year's Sales",
"Orders"."Order Date", "Customer"."Country"
FROM
FROM lists all the tables used in the report, and
each table name is followed by an alias name.
For example, the following shows that the report
is using the
Customer and
Orders tables from
the XTREME database, and in each case, the alias name is the same as the table
name:
FROM
"XTREME"."dbo"."Customer" "Customer"
"XTREME"."dbo"."Orders" "Orders"
INNER JOIN
INNER JOIN specifies any links between tables.
For example, the following shows that the
"Orders" and "Customer" tables are linked on the "Customer ID" field:
INNER JOIN
"XTREME"."dbo"."Orders" "Orders" ON
"Customer"."Customer ID"="Orders"."Customer ID"
WHERE
WHERE specifies the record filters for the
report.
For example, the following shows that the report
is filtered to display records from 'Canada', 'England', or 'USA':
WHERE
("Customer"."Country"='Canada' OR
"Customer"."Country"='England' OR
"Customer"."Country"='USA')
ORDER BY
ORDER BY sorts the data in alphabetic or numeric
ascending or descending order.
For example, the following sorts the records in
ascending order by the values that appear in the 'Customer.Country' and 'Customer.CUSTOMER_NAME' fields:
ORDER BY
"Customer"."Country", "Customer"."CUSTOMER_NAME"