Tables and views
Both tables and views have a common header menu on the right side of the table widget, that contains a number of actions (listed below) that you can perform based on the contents of the table.
When clicking the Graph , the right sidebar graph menu will be open. For more information about the sidebar, please click here.
If you are interested in creating an aggregated report of the table or view, without writing any SQL code, you can do that by clicking the Measurements . Then, simply start adding the columns based on the report by clicking the button +.
Scai detects automatically based on the column data type whether it should be:
- group by - the aggregation will group the results on the values of this column (typically chosen for non-numerical columns)
- pivot - the distinct values of the column will be used to create a pivot table. Each distinct value will then be represented as a separate column in the aggregated results.
- measurement - a mathematical function will be applied to the values of this column based:
- sum (sum)
- average (avg)
- count (count)
- maximum (max)
If you want to change the names of the columns that will be displayed in the aggregated table, simply fill in the alias input after the name of the column.
When satisfied with the selected columns for the aggregated report, click the Save button.
For every table/view you have some helpers in this menu that enable you to remember a set of filters that you commonly use. The following options are available:
- clear - clears all filters/orderings you have applied on the current table
- remember - remembers the current set of applied filters for this table. This set can be automatically applied at a later time by clicking the reload option. Use this if you would like to have the contents of a table ordered/filtered in a certain way each time you access it.
- forget - forgets any remembered filters. Any filters that you might have saved using the remember button will now be lost.
- reload - applies the set of filters that were most recently remember-ed
For every table/view and depending on user permissions, an options menu can be found for the main SQL operations if you click the widget header Options . These are the following:
- create - insert a new record into the table (only for non read-only tables)
- delete - delete one or more records from table (only for non read-only tables)
- insert into - insert the content of the table or view into another table
- create a new table - create a new table based on the contents of the table or view
- merge into - merge the contents of this table or view into another table
- load into - load data from file (only for non read-only tables)
Add new row
Go to table you want to add a new row to, click Options and then create. It will open a modal where the data can be filled in.
Data can be easily deleted from a table by going to that particular table, then clicking Options , then delete. The table will enter in delete mode. The data can be filtered and only those records that match those filters will be deleted from it.
Pay attention that if you CLICK the "Delete" button without any filters, you will delete the entire data permanently!It is recommended that you first apply the filters and, then, click the delete button
Once you are done with removing records, simply click the Go to table to exit the delete mode.
Insert records into another table
If want to insert some rows of the (input) table (or view) into another table (destination table), click Options , then insert into. On the left side, a menu to select the table into which the data should be inserted will appear. Here, you can search for a given table. Click the name of the table into which you want to insert the data. Once that is done, the names of the columns from the destination table will appear.
For each column of the destination table, there are 3 methods of inserting into them:
- by column (checkbox Column selected) - insert the values of a field from the table (or file loaded) into that given column. In this case you would check the Column checkbox and then select the field from the input table (or file) that you would like to map to the given column.
- by value (checkbox Value selected) - insert a static value into that given column for each row inserted. In this case you would check the Value checkbox and then enter the static value in the input that appears.
- by using the sql default values (no selected checkboxes) - insert the database default value into that given column for each row inserted. In this case do not check any of the checkboxes.
- You can set a null value for a given column for each row you insert by clicking the Value checkbox and leaving the input field empty.
- To insert the values of the field 'A' into the column 'name' of a given table, you would check the Column checkbox next to the 'name' label and then select from the list field 'A'.
You can click the Save button, once you have found the table you are interested in and configured how to match the input fields to its columns and limited the rows inserted using filters. If inserting into an existing table was successful (i.e. no errors occurred due to mismatch of data types or the format of the data), you can click Go to table to be directed to the destination table.
Create new table based on content
This can be achieved by clicking Options , then create a new table. It will open a new table with the following menu. On the left side, you can select the name of the schema and the name of the table that will be created. Then, click the Save button.
If you want to update another table with the content of the current table, you can click Options , then click merge into. The widget will be split into two parts:
- left side - a search box with all tables from the database which is used for selecting the table name that will be updated
- right side - the current table, from which rows can be selected based on filters
For ease of use, you can search for tables by using the searchbox and filtering tables following the schema.table_name format.
After a table is selected, you will notice that for each column of the selected table there appear more options, possibly auto-filled, that can be configured:
Key columns are columns that will behave as joining keys when performing the merge. These columns will not have their values updated, but will only help match rows of the table you see on the right side with the rows of the selected table.
Please make sure that the combination of Key columns uniquely identifies the rows of each table. This means that there should be no two rows that have the same values on the Key columns in any tables, both the one you see the content of in the right side, but also in the selected table.
New value columns are columns that will have their values updated during the merge operation.
all other columns will not participate in the merge operation
After deciding which columns will be Keys or New values, for each such column of the selected table you need to choose whether you want to match a column of the current table (that you can see on the right side of the widget) or a constant value.
This complex operation is better explained by means of example. Let's suppose that you have two tables:
Customer(id, name, address, credit_amount) and
OnlineCustomer(id, name, email). You would then like to update the contents of the
Customer table with the data coming from
OnlineCustomer. Let's check how you can do that using the merge operation.
Suppose you would like to set as
address (column of
Customer) the email address of all online customers and also set their
credit_amount to zero. You would then go to the
OnlineCustomer table and click the merge into option. After the new page is loaded, you can select the table you would like to merge the data into, in our case
- You would then make sure that the
idcolumn of the
Customertable is a Key, that it is mapped to a Column of the
OnlineCustomertable and, more specifically to the
idcolumn of the
OnlineCustomer. Thus you would check the Key and Column checkboxes and select the
idcolumn from the drop-down select.
- Next, the
addresscolumn should be checked as a New value, since we want to have its values updated and mapped to the
OnlineCustomertable. Thus, here we check New value and Column and select the
- Finally, the
credit_amountcolumn should be mapped to a constant Value of zero. Thus check New value and Value boxes and enter 0 in the input that appears.
namecolumn should not have any checked boxes
This represents a quick menu to load data into a table. The same steps apply as here.
In case of views, another option will appear which is Show definition. If it is clicked then a console displaying the definition code of the view will be shown and Show definition will be replaced by Hide definition. The code can be modified for a quick modification of the view.
Note: the view definition can be modified only if you are the owner of the view's data source
In the widget containing the view definition, two buttons are present:
- Preview - quick preview of the results of the new modified code
- Save - save the new (modified) definition of the view into the database
If you wish to exit the view mode, simply click the Hide definition button located in the original widget menu.