View and Manage Data in a Data Set
You can view the data in a data set from the data set page. Based on user privileges and the level of permissions that you have on a data set you can edit the data in the data set.
Edit the data set to add or remove rows from a table or to update the data. Use the SQL Query field to edit data or directly edit the data in the grid. You can perform data manipulation language (DML) operations to edit the data. You cannot perform data definition language (DDL) operations.
You can add row-level tags to the data to track the changes. You can filter and search for data in a data set based on row-level tags that you add.
You can reset a data set with data that you edit to a test environment to return the data to a required state.
Rules and Guidelines for Viewing and Managing Data
You can view and edit data in a data set from the Tables tab in a data set page.
Consider the following rules and guidelines when you view or edit data in a data set:
- •The default SQL query is SELECT * FROM.
- •You can use the WHERE clause with SELECT queries.
- •You cannot run a SELECT query with aggregate functions such as COUNT, MIN, MAX.
- •You cannot run a SELECT query with a GROUP BY clause.
- •You cannot run a SELECT query with Joins.
- •If a table or column name contains spaces or special characters, you must enter the name in double quotes (") when you use it in a query.
- •To run a query that uses the WHERE clause with the date data type, you must enter TO_DATE in the SELECT statement.
For example:
SELECT * from INVOICES where INVOICE_DATE=TO_DATE('01/06/2003 00:00:00','mm/dd/yyyy hh24:mi:ss').
- •You cannot run an SQL query with a table alias.
- •You cannot run an SQL query that contains the table name with the owner name.
- •You cannot insert numeric data with data values outside the range of -9223372036854775807 to 9223372036854775807.
- •You cannot use a comma (",") in a tag as the comma is a tag delimiter. Use commas to separate tags.
- •You cannot view or edit data in tables that contain the special character "." in the table name or column name.
- •You cannot view and edit data from an Oracle database that is of BLOB and RAW data type.
- •You cannot view or edit data from any database data type that is mapped to Oracle BLOB and RAW data types.
Viewing Data in a Data Set
You can view data in tables in a data set from the data set page.
1. Open the required data set.
2. Click the Tables tab.
3. Select the table for which you want to view the data.
4. Click the View Data tab in the panel below the list of tables.
The table data appears in a grid.
5. Optional. To view specific data, run an SQL query to select data based on required criteria.
Data that matches the criteria appears in the grid.
6. Optional. If the data contains row-level tags, you can enter the tag name in the Filter Tags field and search for and view data based on tags. You can also select the required option from the View By list to view all tagged or untagged data.
If you first run an SQL query to select data, the filters search for and return data that matches from the results of the SQL query.
Using the SQL Query Field to View and Manage Data
You can run an SQL query to edit the data in a data set.
1. Open the required data set.
2. Click the Tables tab.
3. Select the table that you want to update.
4. Click the View Data tab in the panel below the list of tables.
The table data appears in a grid.
5. Optional. To view specific data, run an SQL query to select data based on required criteria.
Data that matches the criteria appears in the grid.
6. Enter the SQL query to insert, delete, or update the data in the SQL Query field and click Run.
The following image shows the SQL Query field on the View Data tab in the data set page:
You can view the updated data in the grid.
SQL Queries to View and Edit Data
You can run SQL queries to view and edit data in a data set.
The following table lists the queries that you can run along with sample syntax:
Query | Sample Syntax |
|---|
SELECT * | SELECT * FROM <table_name>; |
SELECT | SELECT <column_name>,<column_name> FROM <table_name>; |
SELECT | SELECT <column_name> AS <alias_name> FROM <table_name>; |
INSERT INTO | INSERT INTO <table_name> (column1,column2,column3) VALUES (value1,value2,value3); You must enter the column names. You cannot run the query without the column names. |
UPDATE | UPDATE <table_name> SET column1=value1,column2=value2 |
DELETE FROM | DELETE FROM table_name WHERE <column_name>=<value>; |
DELETE FROM | DELETE FROM table_name; Deletes all data in the table. |
Updating Data in the Grid
You can update the data in required cells directly in the grid.
1. Open the required data set.
2. Click the Tables tab.
3. Select the table for which you want to update the data.
4. Click the View Data tab in the panel below the list of tables.
The table data appears in a grid.
5. Optional. To view specific data, run an SQL query to select data based on required criteria.
Data that matches the criteria appears in the grid.
6. Click to select the row that you want to edit.
The fields in the row become editable.
7. Click the required field and edit the data as required.
8. Click the Save button to save the changes.
The updated data appears in the grid.
Adding Rows to a Data Set Table
You can add rows to a table in a data set.
1. Open the required data set.
2. Click the Tables tab.
3. Select the table that you want to update.
4. Click the View Data tab in the panel below the list of tables.
The table data appears in a grid.
5. Click the Add Row button in the right-hand corner of the panel.
A new row appears in the grid.
6. Enter the data in the required data fields.
You cannot add a blank row. Enter data in at least one column.
7. Click the Save button to save the row to the table.
The row of data is added to the table and appears in the grid.
Deleting Rows in a Data Set Table
You can delete rows from a table in a data set.
1. Open the required data set.
2. Click the Tables tab.
3. Select the table that you want to update.
4. Click the View Data tab in the panel below the list of tables.
The table data appears in a grid.
5. Click the check box to select the rows that you want to delete.
6. Click the Delete Row button in the right-hand corner of the panel.
7. Click OK to confirm the delete.
The rows are deleted from the table.
Managing Row-level Tags in a Data Set Table
You can add, delete, and update row-level tags to data in a data set.
1. Open the required data set.
2. Click the Tables tab.
3. Select the table for which you want to update the data.
4. Click the View Data tab in the panel below the list of tables.
The table data appears in a grid.
5. Optional. To view specific data, run an SQL query to select data based on required criteria.
Data that matches the criteria appears in the grid.
6. Optional. Use the Filter Tags field to view data with specific tags, or use the Filter By option to view all tagged or untagged data.
Data that matches the criteria appears in the grid.
7. To select the rows to which you want to add, delete, or update tags, click the required check boxes.
8. Click the Update Tags button in the right-hand corner of the panel.
The Update Tags window opens.
9. Add or delete tags or update tags and then click OK.
Use commas to separate tags. The tags are added, deleted, or updated in the selected rows in the table.
