Working with database tables

You can open any database table that is accessible to you in your workspace using a default editor, the Table Editor, the Table Viewer or the Visual Query Editor.

1. Right-click the table in any view and select Open With.
2. Click one of the following items:
   • Table Viewer to display the contents of the table in the Query Results view. When a table is opened with Table Viewer, a query is created, run and the results are returned.
   • Visual Query Editor to display the table in the Query Builder editor. When the table is opened with the Visual Query Editor, a query is created and displayed in the Query Builder editor. The query is not run and results are not returned. Users can modify the query using the Query Builder editor.
   • Table Editor to display the table in edit mode.
     The default view for the Table Editor is the Results view, as indicated by the Results tab in the editing area.

     Note: Permission to use the Table Editor is set by the administrator. Click View > Resource Limits and select the Options tab to verify whether you have permission to use the Table Editor. If you have permission to edit tables, the Enable table editing property is set to Yes.
     You can use the Table Editor to search for, add, edit, and delete the data that is stored in the tables without writing SQL statements. You can also save the Table Editor query objects (SQL query along with the sorting and filtering options) for future use. You can open the saved Table Editor query object to resume working with the same SQL query that includes the previously used row conditions and sorting options. You can also save different types of row conditions and sorting options as multiple Table Editor query objects. This enables you to conveniently reuse the different views in future.

     Note: The Table Editor is not available for Spark and Hive data sources.

     Using the Results menu item to work with database tables

     The Results menu item is activated when viewing a table in the Results view of the Table Editor.

     The Results menu provides access to most of the functionality in the Table Editor.

     To access and use Result menu options:

     1. With the table opened in the Table Editor, select the Results tab.
     2. Click Results from the menu list to display available options.
        The following table lists the Results menu options and describes the purpose of each option.

        Option	Purpose
        Retrieve All	Select this option to retrieve all rows from the table.
        Find	Select this option to search the table for specific data. Enter the search criteria in the Find dialog and click Find next.
        Insert Row	Select this option to insert a table row.
        Delete Row	Select this option to remove a table row. The row that you select for deletion is marked with an asterisk and is removed if you commit the change to the table.
        Edit Row	Select this option to edit row data.
        Commit	Select this option to manually commit the changes you have made to the table
        Roll back	Select this option to undo changes that you have made to the table
        Immediate Commit	Select this option to enable the immediate commit function, which results in changes to the table being committed immediately.

     Editing content directly in the table

     You can edit table data directly in the Table Editor in the following ways:

     • Placing your cursor in the cell containing the information you want to edit, right-clicking and selecting Edit from the pop-up menu.

       In the Cell value window, enter your changes and click OK. Click OK again to confirm that the updates will committed to the database.

       Click Cancel if you want to roll back the updates.

     • Placing your cursor in the cell and pressing Enter or by double-clicking in the table cell.

     Changing the table structure

     You change the structure of a table (for example, the number and data type of columns) directly in the Table Editor in the following ways:

     • Deleting a row or multiple rows:
       1. Click the row number of the row that you want to delete. To select multiple rows, press the Shift key while clicking the table rows.
       2. Right-click and select Delete Row from the pop-up menu.
       3. If you are sure that you want to delete the row from the table, click Yes. Click No if you decide not to delete the row from the table.
     • Editing a table row:
       1. Click the row number of the row that you want to edit.
       2. Right-click and select Edit Row from the pop-up menu.

          The row and its content are displayed in an Edit Row window. The Edit Row window title contains the row number from the results grid indicating the current row that is being edited.

       3. Make changes to data in the Value of the Edit Row window.

          Note: The cell value to be edited might have Auto-Generated or Calculated values. These values are computed by the system and hence are disabled.
       4. You can choose to click the Prev Row and Next Row buttons on the Edit Row window to update the previous and next row contents of the table.

          If the Immediate Commit option is selected on the Results menu, a Confirm Updates dialog box is displayed when you navigate to the previous or next rows of the table. On the Confirm Updates dialog box, click OK to commit the changes that you made to the current row, or click Cancel to ignore the changes that you made to the current row.

          If the Immediate Commit option is cleared on Results the menu, all the changes that you make to the table are stored locally until you choose to commit the changes to the database using the Commit option on the Results menu. To disregard the locally stored changes, you can use the Roll back option on the Results menu.

          Note: The Results tab is not updated or refreshed when navigating the rows of a table using the Prev Row and Next Row buttons. The updates that you make to the contents of a table are reflected in the Results tab only after you close the Edit Row window.
       5. After completing the desired updates to the table, click OK on the Edit Row window.
       6. If the Immediate Commit option is selected on the Results menu, a Confirm Updates dialog box is displayed. On the Confirm Updates dialog box, click OK to commit the changes that you made to the current row, or click Cancel to ignore the changes that you made to the current row.
     • Inserting a row:

       Select Insert Row from the Results menu.

       The Insert Row dialog box opens. The Insert Row dialog box might have cells marked as <Auto-Generated> or <Calculated>. These cells are disabled and you will not be able to specify the values for these cells, as these values are computed by the system.

       • An <Auto-Generated> column generates a unique number when a record is inserted in the table.
       • A <Calculated> column derives its value using an expression, rather than being specified through an insert or update operation.

       If the option Immediate Commit on the menu is selected, the changes that you make to the table are applied to the database as you make them and click OK.

       If the option Immediate Commit is cleared then the changes that you make are stored locally and are applied all at once when if you select the Commit option, or they are disregarded all at once if select the Roll back option.

       Otherwise, when you close the editor object you will get a message to Commit or Rollback all pending changes.

       The inserted row will be the last row of the result set after the commit is done.

       Note: If you select a row before you click Insert Row then the Insert dialog will be pre-populated with the content of the row so that you can use it as a template for a new row.

     To specify how to display the nonnumeric table data, place your cursor in the cell containing text string for which you want to set the display format. Right-click, select Edit from the pop-up menu and then click Advanced. From the Format mode list, select Hexadecimal to display the data in hexadecimal format or Binary to display the data in binary format. The Zoom window displays samples of the data in the format that you have chosen.

     You can use the Prompted Query view of the Table Editor to set the sort and row conditions in the table. For information about setting sort and row conditions, see Specifying sort conditions for tables and Specifying row conditions for tables.

   • Visual Query Editor to display the table in the Query Builder editor. When the table is opened with the Visual Query Editor, a query is created and displayed in the Query Builder editor. The query is not run and results are not returned. Users can modify the query using the Query Builder editor.
   • Default Editor to display the table using the editor that was last used.