Users Guide
Defining DataWindow Objects

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.

  
  
  

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.

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

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:

     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.

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.

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.

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.

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

   The Join dialog box displays:

   
   
   

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

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 to retrieve 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:

   SELECT * from employee WHERE dept_id IN (100, 200, 500)

   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:

   int x[3]
   // Now populate the array with values
   // such as x[1] = sle_dept.Text, and so on,
   // then retrieve the data, as follows.
   dw_1.Retrieve(x)
   Integer x[]= new Integer[3];
   x[0]=new Integer(100);
   x[1]=new Integer(200);
   x[2]=new Integer(500);
   dw1.retrieve(x);

   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 if x[0] = 100, x[1] = 200, and x[2] = 500).

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

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.

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

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

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

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

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

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 ID, 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.

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.

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.