In specifying data for a report, you have more options for specifying complex SQL statements when you use SQL Select as the data source. When you choose SQL Select, you go to the SQL Select painter, where you can paint a SELECT statement that includes the following:
-
More than one table
-
Selection criteria (WHERE clause)
-
Sorting criteria (ORDER BY clause)
-
Grouping criteria (GROUP BY and HAVING clauses)
-
Computed columns
-
One or more arguments to be supplied at runtime
Saving your work as a query
While in the SQL Select painter, you can save the current SELECT statement as a query by selecting File>Save As from the menu bar. Doing so allows you to easily use this data specification again in other reports.
For more information about queries, see Defining queries.
To define the data using SQL Select:
-
Click SQL Select in the Choose Data Source dialog box in the wizard and click Next.
The Select Tables dialog box displays.
-
Select the tables and/or views that you will use in the report.
For more information, see Selecting tables and views.
-
Select the columns to be retrieved from the database.
For more information, see Selecting columns.
-
Join the tables if you have selected more than one.
For more information, see Joining tables.
-
Select retrieval arguments if appropriate.
For more information, see Using retrieval arguments.
-
Limit the retrieved rows with WHERE, ORDER BY, GROUP BY, and HAVING criteria, if appropriate.
For more information, see Specifying selection, sorting, and grouping criteria.
-
If you want to eliminate duplicate rows, select Distinct from the Design menu. This adds the DISTINCT keyword to the SELECT statement.
-
Click the Return button on the PainterBar.
You return to the wizard to complete the definition of the report.
-
Click OK.
After you have chosen SQL Select, the Select Tables dialog box displays in front of the Table Layout view of the SQL Select painter. What tables and views display in the dialog box depends on the DBMS. For some DBMSs, all tables and views display, whether or not you have authorization. Then, if you select a table or view you are not authorized to access, the DBMS issues a message.
For ODBC databases, the tables and views that display depend on the driver for the data source. SQL Anywhere does not restrict the display, so all tables and views display, whether or not you have authorization.
To select the tables and views:
-
Do one of the following:
-
Click the name of each table or view you want to open.
Each table you select is highlighted. (To deselect a table, click it again.) Click the Open button to close the Select Tables dialog box.
-
Double-click the name of each table or view you want to open.
-
-
Each object opens immediately behind the Select Tables dialog box. Click the Cancel button to close the Select Tables dialog box.
Representations of the selected tables and views display. You can move or size each table to fit the space as needed.
Below the Table Layout view, several tabbed views also display by default. You use the views (for example, Compute, Having, Group) to specify the SQL SELECT statement in more detail. You can turn the views on and off from the View menu on the menu bar.
Specifying what is displayed
You can display the label and datatype of each column in the tables (the label information comes from the extended attribute system tables). If you need more space, you can choose to hide this information.
To hide or display comments, datatypes, and labels:
-
Position the pointer on any unused area of the Table Layout view and select Show from the pop-up menu.
A cascading menu displays.
-
Select or clear Datatypes, Labels, or Comments as needed.
Colors in the SQL Select painter
The colors used by the SQL Select painter to display the Table Layout view background and table information are specified in the Database painter. You can also set colors for the text and background components in the table header and detail areas.
For more information about specifying colors in the Database painter, see Modifying database preferences.
Adding and removing tables and views
You can add tables and views to your Table Layout view at any time.
|
To do this |
Do this |
|---|---|
|
Add tables or views |
Click the Tables button in the PainterBar and select tables or views to add |
|
Remove a table or view |
Display its pop-up menu and select Close |
|
Remove all tables and views |
Select Design>Undo All from the menu bar |
You can also remove individual tables and views from the Table Layout view, or clear them all at once and begin selecting a new set of tables.
How InfoMaker joins tables
If you select more than one table in the SQL Select painter, InfoMaker joins columns based on their key relationship.
For information about joins, see Joining tables.
You can click each column you want to include from the table representations in the Table Layout view. InfoMaker highlights selected columns and places them in the Selection List at the top of the SQL Select painter.
To reorder the selected columns:
-
Drag a column in the Selection List with the mouse. Release the mouse button when the column is in the proper position in the list.
To select all columns from a table:
-
Move the pointer to the table name and select Select All from the pop-up menu.
To include computed columns:
-
Click the Compute tab to make the Compute view available (or select View>Compute if the Compute view is not currently displayed).
Each row in the Compute view is a place for entering an expression that defines a computed column.
-
Enter one of the following:
-
An expression for the computed column. For example: salary/12
-
A function supported by your DBMS. For example, the following is a SQL Anywhere function:
substr("employee"."emp_fname",1,2)
You can display the pop-up menu for any row in the Compute view. Using the pop-up menu, you can select and paste the following into the expression:
-
Names of columns in the tables used in the report, form, or pipeline
-
Any retrieval arguments you have specified
-
Functions supported by the DBMS
-
About these functions
The functions listed here are provided by your DBMS. They are not InfoMaker functions. This is so because you are now defining a SELECT statement that will be sent to your DBMS for processing.
-
-
Press the Tab key to get to the next row to define another computed column, or click another tab to make additional specifications.
InfoMaker adds the computed columns to the list of columns you have selected.
About computed columns and computed fields
Computed columns you define in the SQL Select painter are added to the SQL statement and used by the DBMS to retrieve the data. The expression you define here follows your DBMS's rules.
You can also choose to define computed fields, which are created and processed dynamically by InfoMaker after the data has been retrieved from the DBMS. There are advantages to doing this. For example, work is offloaded from the database server, and the computed fields update dynamically as data changes in the report. (If you have many rows, however, this updating can result in slower performance.) For more information, see Enhancing Reports
As you specify the data for the report in the SQL Select painter, InfoMaker generates a SQL SELECT statement. It is this SQL statement that will be sent to the DBMS when you retrieve data into the report. You can look at the SQL as it is being generated while you continue defining the data for the report.
To display the SQL statement:
-
Click the Syntax tab to make the Syntax view available, or select View>Syntax if the Syntax view is not currently displayed.
You may need to use the scroll bar to see all parts of the SQL SELECT statement. This statement is updated each time you make a change.
Editing the SELECT statement syntactically
Instead of modifying the data source graphically, you can directly edit the SELECT statement in the SQL Select painter.
Converting from syntax to graphics
If the SQL statement contains unions or the BETWEEN operator, it may not be possible to convert the syntax back to graphics mode. In general, once you convert the SQL statement to syntax, you should maintain it in syntax mode.
To edit the SELECT statement:
-
Select Design>Convert to Syntax from the menu bar.
InfoMaker displays the SELECT statement in a text window.
-
Edit the SELECT statement.
-
Do one of the following:
-
Select Design>Convert to Graphics from the menu bar to return to the SQL Select painter.
-
Click the Return button to return to the wizard if you are building a new report, or to the Report painter if you are modifying an existing report.
-
If the report will contain data from more than one table, you should join the tables on their common columns. If you have selected more than one table, InfoMaker joins columns according to whether they have a key relationship:
-
Columns with a primary/foreign key relationship are joined automatically.
-
Columns with no key relationship are joined, if possible, based on common column names and types.
InfoMaker links joined tables in the SQL Select painter Table Layout view. InfoMaker joins can differ depending on the order in which you select the tables, and sometimes the InfoMaker best-guess join is incorrect, so you may need to delete a join and manually define a join.
To delete a join:
-
Click the join operator connecting the tables.
The Join dialog box displays.
-
Click Delete.
To join tables:
-
Click the Join button in the PainterBar.
-
Click the columns on which you want to join the tables.
-
To create a join other than an equality join, click the join operator in the Table Layout view.
The Join dialog box displays:
-
Select the join operator you want and click OK.
If your DBMS supports outer joins, and the Allow Cross Product option is set in the Query Governor, outer join options also display in the Join dialog box.
About the Query Governor
You can use the Query Governor to set data selection and retrieval options. For more information about the Query Governor, see Using the Query Governor.
All InfoMaker database interfaces provide support for ANSI SQL-92 outer join SQL syntax generation. InfoMaker supports both left and right outer joins in graphics mode in the SQL Select painter, and full outer and inner joins in syntax mode. Depending on your database interface, you might need to set the OJSyntax DBParm to enable ANSI outer joins. For more information, see OJSyntax in the online Help.
The syntax for ANSI outer joins is generated according to the following BNF (Backus Naur form):
OUTER-join ::=
table-reference {LEFT | RIGHT} OUTER JOIN table-reference ON search-condition
table-reference ::=
table_view_name [correlation_name] | OUTER-joinOrder of evaluation and nesting
In ANSI SQL-92, when nesting joins, the result of the first outer join (determined by order of ON conditions) is the operand of the outer join that follows it. In InfoMaker, an outer join is considered to be nested if the table-reference on the left of the JOIN has been used before within the same outer join nested sequence.
The order of evaluation for ANSI syntax nested outer joins is determined by the order of the ON search conditions. This means that you must create the outer joins in the intended evaluation order and add nested outer joins to the end of the existing sequence, so that the second table-reference in the outer join BNF above will always be a table_view_name.
Nesting example
For example, if you create a left outer join between a column in Table1 and a column in Table2, then join the column in Table2 to a column in Table3, the product of the outer join between Table1 and Table2 is the operand for the outer join with Table3.
For standard database connections, the default generated syntax encloses the outer joins in escape notation {oj ...} that is parsed by the driver and replaced with DBMS-specific grammar:
SELECT Table1.col1, Table2.col1, Table3.col1
FROM {oj {oj Table1 LEFT OUTER JOIN Table2 ON Table1.col1 = Table2.col1}
LEFT OUTER JOIN Table3 ON Table2.col1 = Table3.col1} Table references
Table references are considered equal when the table names are equal and there is either no alias (correlation name) or the same alias for both. Reusing the operand on the right is not allowed, because ANSI does not allow referencing the table_view_name twice in the same statement without an alias.
Determining left and right outer joins
When you create a join condition, the table you select first in the painter is the left operand of the outer join. The table that you select second is the right operand. The condition you select from the Joins dialog box determines whether the join is a left or right outer join.
For example, suppose you select the dept_id column in the employee table, then select the dept_id column in the department table, then choose the following condition:
employee.dept_id = department.dept_id and rows from department that have no employee
The syntax generated is:
SELECT employee.dept_id, department.dept_id
FROM {oj "employee" RIGHT OUTER JOIN "department" ON "employee"."dept_id" = "department"."dept_id"}If you select the condition, rows from employee that have no department, you create a left outer join instead.
Equivalent statements
The syntax generated when you select table A then table B and create a left outer join is equivalent to the syntax generated when you select table B then table A and create a right outer join.
For more about outer joins, see your DBMS documentation.
If you want a report, form, or pipeline to prompt for criteria to determine which rows to retrieve when you preview the report, run the form, or execute the pipeline, you can use retrieval arguments in the SQL SELECT statement. If you define the data source without defining retrieval arguments and decide later that you need arguments, you can return to the Select painter to define the arguments.
Another way to prompt for retrieval criteria
You can select View>Column Specifications from the menu bar. In the Column Specification view, a column of check boxes next to the columns in the data source lets you identify the columns to be prompted for. This, like the Retrieval Arguments prompt, calls the Retrieve method.
See Enhancing Reports and Enhancing Forms
For example, suppose you are creating a report that provides information about any employee. When you are defining the report in the Report painter, you pass the employee ID as an argument (placeholder). When you run the report, you are prompted for the employee ID, you supply the ID number, and the report displays information about that employee.
To define retrieval arguments:
-
Make sure you are in the Select painter (from the Report painter or the Form painter, select Design>Data Source from the menu bar).
-
In the SQL Select painter, select Design>Retrieval Arguments from the menu bar.
-
Enter a name and datatype for each argument.
The first character must be alphabetic (a-z); subsequent characters can be alphanumeric (a-z, 1-9), an underscore ( _ ), or a dollar sign ($).
-
Click the Add button to define additional arguments as needed, and click OK when done.
Specifying an array as a retrieval argument
You can specify an array of values as your retrieval argument. For example, suppose you want a report that shows employee names and IDs for a few departments and prompts you to enter the IDs when you preview the report.
In the Specify Retrieval Arguments dialog box, choose the type of array from the Type drop-down list. For the case of department IDs, the array is a number array.
After you define retrieval arguments, you must reference the arguments in the Where view or Having view in the Select painter.
To reference an argument means to refer to the argument in an expression so that InfoMaker can use it as a placeholder until you provide the actual value. For example, if a report is retrieving all rows from the Department table where the DeptID matches a value provided, the WHERE clause looks something like this:
WHERE DeptID = :Entered_id
where Entered_id was defined previously as a retrieval argument in the Specify Retrieval Arguments dialog box.
How retrieval arguments are referenced
In SQL statements, variables (called host variables) are always prefaced with a colon to distinguish them from column names.
A retrieval argument is a variable. To reference the retrieval argument Entered_id in a SQL statement, enter:
:Entered_id
Referencing an array
To reference an array, use the IN operator and reference the retrieval argument in the WHERE or HAVING clause.
For the case of the array defined as deptarray, the expression in the Where view will look like the following expression. You can paste the :deptarray argument using the pop-up menus in the value area of the Where view:
Supplying values for an array argument
When you preview the report, you are prompted to supply the department values. InfoMaker retrieves rows that match one of the set of values that you supply. For example, if you supply the department IDs 100, 200, and 500 as shown, your report displays information about these departments:
In the SELECT statement associated with a report, you can add selection, sorting, and grouping criteria that are added to the SQL statement and processed by the DBMS as part of the retrieval.
|
To do this |
Use this clause |
|---|---|
|
Limit the data that is retrieved from the database |
WHERE |
|
Sort the retrieved data before it is brought into the report |
ORDER BY |
|
Group the retrieved data before it is brought into the report |
GROUP BY |
|
Limit the groups specified in the GROUP BY clause |
HAVING |
Dynamically selecting, sorting, and grouping data
Selection, sorting, and grouping criteria that you define in the SQL Select painter are added to the SQL statement and processed by the DBMS as part of the retrieval. You can also define selection, sorting, and grouping criteria that are created and processed dynamically by InfoMaker after data has been retrieved from the DBMS.
For more information, see Filtering, Sorting, and Grouping Rows
You can limit the rows that are retrieved into the report by specifying selection criteria that correspond to the WHERE clause in the SELECT statement.
For example, if you are retrieving information about employees, you can limit the employees to those in Sales and Marketing, or to those in Sales and Marketing who make more than $50,000.
To define WHERE criteria:
-
Click the Where tab to make the Where view available (or select View>Where if the Where view is not currently displayed).
Each row in the Where view is a place for entering an expression that limits the retrieval of rows.
-
Click in the first row under Column to display columns in a drop-down list, or select Columns from the pop-up menu.
-
Select the column you want to use in the left-hand side of the expression.
The equality (=) operator displays in the Operator column.
Using a function or retrieval argument in the expression
To use a function, select Functions from the pop-up menu and click a listed function. These are the functions provided by the DBMS.
To use a retrieval argument, select Arguments from the pop-up menu. You must have defined a retrieval argument already.
-
(Optional) Change the default equality operator.
Enter the operator you want, or click to display a list of operators and select an operator.
-
Under Value, specify the right-hand side of the expression. You can:
-
Type a value.
-
Paste a column, function, or retrieval argument (if there is one) by selecting Columns, Functions, or Arguments from the pop-up menu.
-
Paste a value from the database by selecting Value from the pop-up menu, then selecting a value from the list of values retrieved from the database. (It may take some time to display values if the column has many values in the database.)
-
Define a nested SELECT statement by selecting Select from the pop-up menu. In the Nested Select dialog box, you can define a nested SELECT statement. Click Return when you have finished.
-
-
Continue to define additional WHERE expressions as needed.
For each additional expression, select a logical operator (AND or OR) to connect the multiple boolean expressions into one expression that InfoMaker evaluates as true or false to limit the rows that are retrieved.
-
Define sorting (Sort view), grouping (Group view), and limiting (Having view) criteria as appropriate.
-
Click the Return button to return to the Report painter.
Defining ORDER BY criteria
You can sort the rows that are retrieved into the report by specifying columns that correspond to the ORDER BY clause in the SELECT statement.
For example, if you are retrieving information about employees, you can sort on department, and then within each department, you can sort on employee ID.
To define ORDER BY criteria:
-
Click the Sort tab to make the Sort view available (or select View>Sort if the Sort view is not currently displayed).
The columns you selected display in the order of selection. You might need to scroll to see your selections.
-
Drag the first column you want to sort on to the right side of the Sort view.
This specifies the column for the first level of sorting. By default, the column is sorted in ascending order. To specify descending order, clear the Ascending check box.
-
Continue to specify additional columns for sorting in ascending or descending order as needed.
You can change the sorting order by dragging the selected column names up or down. With the following sorting specification, rows will be sorted first by department ID, then by employee ID:
-
Define limiting (Where view), grouping (Group view), and limiting groups (Having view) criteria as appropriate.
-
Click the SQL Select button to return to the Report painter.
Defining GROUP BY criteria
You can group the retrieved rows by specifying groups that correspond to the GROUP BY clause in the SELECT statement. This grouping happens before the data is retrieved into the report. Each group is retrieved as one row into the report.
For example, if in the SELECT statement you group data from the Employee table by department ID, you will get one row back from the database for every department represented in the Employee table. You can also specify computed columns, such as total and average salary, for the grouped data. This is the corresponding SELECT statement:
SELECT dept_id, sum(salary), avg(salary) FROM employee GROUP BY dept_id
If you specify this with the Employee table in the PB Demo DB, you get five rows back, one for each department.
For more about GROUP BY, see your DBMS documentation.
To define GROUP BY criteria:
-
Click the Group tab to make the Group view available (or select View>Group if the Group view is not currently displayed).
The columns in the tables you selected display in the left side of the Group view. You might need to scroll to see your selections.
-
Drag the first column you want to group onto the right side of the Group view.
This specifies the column for grouping. Columns are grouped in the order in which they are displayed in the right side of the Group view.
-
Continue to specify additional columns for grouping within the first grouping column as needed.
To change the grouping order, drag the column names in the right side to the positions you want.
-
Define sorting (Sort view), limiting (Where view), and limiting groups (Having view) criteria as appropriate.
-
Click the Return button to return to the Report painter.
Defining HAVING criteria
If you have defined groups, you can define HAVING criteria to restrict the retrieved groups. For example, if you group employees by department, you can restrict the retrieved groups to departments whose employees have an average salary of less than $50,000. This corresponds to:
SELECT dept_id, sum(salary), avg(salary) FROM employee GROUP BY dept_id HAVING avg(salary) < 50000
If you specify this with the Employee table in the PB Demo DB, you will get three rows back, because there are three departments that have average salaries less than $50,000.
To define HAVING criteria:
-
Click the Having tab to make the Having view available (or select View>Having if the Having view is not currently displayed).
Each row in the Having view is a place for entering an expression that limits which groups are retrieved. For information on how to define criteria in the Having view, see the procedure in Defining WHERE criteria.
