Using SQL Select

In specifying data for a DataWindow object, 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 DataWindows.

For more information about queries, see “Defining queries “.

To define the data using SQL Select:

1. Click SQL Select
   in the Choose Data Source dialog box in the wizard and click Next.

   The Select Tables dialog box displays.

2. Select the tables and/or views that you
   will use in the DataWindow object.

   For more information, see “Selecting tables and views”.

3. Select the columns to be retrieved from the database.

   For more information, see “Selecting columns”.

4. Join the tables if you have selected more than
   one.

   For more information, see “Joining tables”.

5. Select retrieval arguments if appropriate.

   For more information, see “Using retrieval arguments”.

6. 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”.

7. If you want to eliminate duplicate rows, select
   Distinct from the Design menu. This adds the DISTINCT keyword
   to the SELECT statement.

8. Click the Return button on the PainterBar.

   You return to the wizard to complete the definition of the DataWindow object.

Selecting tables and views

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. Adaptive Server Anywhere does not restrict
the display, so all tables and views display, whether or not you
have authorization.

To select the tables and views:

1. 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:

1. Position the pointer on any unused area of the
   Table Layout view and select Show from the pop-up menu.

   A cascading menu displays.

2. 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.

Table 18-3: Adding tables and views in the SQL Select painter

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 PowerBuilder joins tables

If you select more than one table in the SQL Select painter, PowerBuilder joins columns
based on their key relationship.

For information about joins, see “Joining tables”.

Selecting columns

You can click each column you want to include from the table
representations in the Table Layout view. PowerBuilder highlights
selected columns and places them in the Selection List at the top
of the SQL Select painter.

To reorder the selected columns:

1. 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:

1. Move the pointer to the table name and
   select Select All from the pop-up menu.

To include computed columns:

1. 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.

2. 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:
     PowerBuilder

     substr("employee"."emp_fname",1,2)

     1	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 DataWindow 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 PowerBuilder functions. This is so because you are now
     defining a SELECT statement that will be sent
     to your DBMS for processing.
     

3. Press the Tab key to get to the next row to define
   another computed column, or click another tab to make additional
   specifications.

   PowerBuilder 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 PowerBuilder 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 DataWindow object. (If
you have many rows, however, this updating can result in slower
performance.) For more information, see Chapter 19, “Enhancing DataWindow Objects .”

Displaying the underlying SQL statement

As you specify the data for the DataWindow object in the SQL Select painter, PowerBuilder generates
a SQL SELECT statement.
It is this SQL statement that will
be sent to the DBMS when you retrieve data into the DataWindow object. You
can look at the SQL as it is
being generated while you continue defining the data for the DataWindow object.

To display the SQL statement:

1. 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:

1. Select Design>Convert to Syntax
   from the menu bar.

   PowerBuilder displays the SELECT statement
   in a text window.

2. Edit the SELECT statement.

3. 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 DataWindow object, or to the DataWindow painter if you
     are modifying an existing DataWindow object.

Joining tables

If the DataWindow object 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, PowerBuilder 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.

PowerBuilder links joined tables in the SQL Select painter Table Layout
view. PowerBuilder joins can differ depending on the order in which
you select the tables, and sometimes the PowerBuilder best-guess join
is incorrect, so you may need to delete a join and manually define
a join.

To delete a join:

1. Click the join operator connecting the
   tables.

   The Join dialog box displays.

2. Click Delete.

To join tables:

1. Click the Join button in the PainterBar.

2. Click the columns on which you want to join the
   tables.

3. To create a join other than an equality join,
   click the join operator in the Table Layout view.

   The Join dialog box displays:

   

4. Select the join operator you want and click OK.

   If your DBMS supports outer joins, outer join options also
   display in the Join dialog box.

Using ANSI outer joins

All PowerBuilder database interfaces provide support for ANSI SQL-92 outer join SQL syntax generation. PowerBuilder 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):

Order 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 PowerBuilder,
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:

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:

The syntax generated is:

If you select the condition with rows
from department that have no employee, you
create a right 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.

Using retrieval arguments

If you know which rows will be retrieved into the DataWindow
object at runtime—that is, if you can fully specify the SELECT statement
without having to provide a variable—you do not need to
specify retrieval arguments.

Adding retrieval arguments

If you decide later that you need arguments, you can return
to the SQL Select painter to define the arguments.

Defining retrieval arguments in the DataWindow painter 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
users should be prompted for. This, like the Retrieval Arguments
prompt, calls the Retrieve method.

See Chapter 19, “Enhancing DataWindow Objects .”

If you want the user to be prompted to identify which rows
to retrieve, you can define retrieval arguments when defining the SQL SELECT statement.
For example, consider these situations:

• Retrieving the row in the Employee table for an
  employee ID entered into a text box. You must pass that information
  to the SELECT statement as an argument at runtime.
• Retrieving all rows from a table for a department
  selected from a drop-down list. The department is passed
  as an argument at runtime.
  Using retrieval arguments at runtime If a DataWindow object has retrieval arguments, call the Retrieve method of
  the DataWindow control to retrieve data at runtime and pass the arguments
  in the method.
  

To define retrieval arguments:

1. In the SQL Select painter, select Design>Retrieval
   Arguments from the menu bar.

2. Enter a name and select a datatype for each argument.

   You can enter any valid SQL identifier
   for the argument name. The position number identifies the argument
   position in the Retrieve method you code in a
   script that retrieves data into the DataWindow object.

3. Click Add 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.
Choose the type of array from the Type drop-down list in the Specify
Retrieval Arguments dialog box. You specify an array if you want
to use the IN operator in your WHERE clause
to retrieve rows that match one of a set of values. For example:

retrieves all employees in department 100, 200, or 500. If
you want your user to specify the list of departments to retrieve,
you define the retrieval argument as a number array (such as 100,
200, 500).

In the code that does the retrieval, you declare an array
and reference it in the Retrieve method, as in:

PowerBuilder passes the appropriate comma-delimited list to
the method (such as 100, 200, 500 if x[1] = 100,
x[2] = 200, and x[3] = 500 ).

When building the SELECT statement, you
reference the retrieval arguments in the WHERE or HAVING clause,
as described in the next section.

Specifying selection, sorting, and grouping criteria

In the SELECT statement associated with
a DataWindow object, 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.

Table 18-4: Adding selection, sorting, and grouping criteria to the SELECT statement

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 DataWindow object	ORDER BY
Group the retrieved data before it is brought into the DataWindow object	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 PowerBuilder after data
has been retrieved from the DBMS.

For more information, see Chapter 23, “Filtering, Sorting, and
Grouping Rows .”

Referencing retrieval arguments

If you have defined retrieval arguments, you reference them
in the WHERE or HAVING clause.
In SQL statements, variables
(called host variables) are always prefaced with a colon to distinguish
them from column names.

For example, if the DataWindow object is retrieving all rows from
the Department table where the dept_id matches
a value provided by the user at runtime, your WHERE clause
will look something like this:

where Entered_id was defined
previously as an argument in the Specify Retrieval Arguments dialog
box.

Referencing arrays Use the IN operator and reference the retrieval
argument in the WHERE or HAVING clause.

For example, if you reference an array defined as deptarray,
the expression in the WHERE view might look like
this:

You need to supply the parentheses yourself.

Defining WHERE criteria

You can limit the rows that are retrieved into the DataWindow object 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:

1. 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.

2. Click in the first row under Column to display
   columns in a drop-down list, or select Columns from the pop-up menu.

3. 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.

4. (Optional) Change the default equality operator.

   Enter the operator you want, or click to display a list of
   operators and select an operator.

5. 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.

6. 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 PowerBuilder evaluates
   as true or false to limit the rows that are retrieved.

7. Define sorting (Sort view), grouping (Group view),
   and limiting (Having view) criteria as appropriate.

8. Click the Return button to return to the DataWindow painter.

Defining ORDER BY criteria

You can sort the rows that are retrieved into the DataWindow object 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:

1. 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.

2. 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.

3. 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 name, then by employee ID:

   

4. Define limiting (Where view), grouping (Group
   view), and limiting groups (Having view) criteria as appropriate.

5. Click the SQL Select
   button to return to the DataWindow 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 DataWindow object. Each group is retrieved as one row into the DataWindow object.

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:

If you specify this with the Employee table
in the EAS 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:

1. 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.

2. 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.

3. 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.

4. Define sorting (Sort view), limiting (Where view),
   and limiting groups (Having view) criteria as appropriate.

5. Click the Return button to return to the DataWindow 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:

If you specify this with the Employee table
in the EAS 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:

1. 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”.

---