The view builder assists you when analyzing data that has been aggregated and kept inside the database. The result of your analysis can be saved in the form of a view.
You can create, alter or drop (or delete) views. You also can run SQL queries and build many different views combining across one or more tables.
You will have
How to use View Builder
1. From the menu, select View Builder.
Your databases will be listed.
2. Click the arrow next to the database to show the tables that you can create a view for.
3. Click a table to work with.
4. Write the SQL for your view in the black text window.
5. Click the arrow to run the query.
6. Save your view by clicking the 'Save' icon found below.
Note: Once you have created a view of a table, you will not see the view listed under the Databases tab in Manage data. However, when you proceed to create a data package, you will be able to select from a list of all tables and views you have created.
Make sure you prefix your database name with your Organization ID
- Refer to screenshot below. All organizations will have a unique id.
- If database names are not prefixed with your Organization ID and run a query, the ‘Query result’ tab will display an error.
- For example, if your Organization ID is o_ABCDEFGHIJKL a query could be:
“SELECT * FROM o_ABCDEFGHIJKL_<database name>.<table name>”
To cancel a query
- Open a new ‘View Builder’ tab and go to “Queries History”
- Click the three dots next to your specific query and select ‘Remove’.
To query a database with incorrect data types
- Use the CAST() function, this enables you to convert your data from one data type to another.
- This is useful when there is an incorrect classification of data types, which may prevent you from performing certain operations such as aggregation or filtering operations.
- For example, you may want to query the SUM of a column yet the data is classified as 'strings'. You would need to cast the column as an INT (integer) or FLOAT before performing the operation.
- An example of how to use the CAST() function:
SELECT SUM(CAST( <field> AS <data type> ))
Column Data Type Descriptions
Common Error Messages
If you are unable to resolve an error, please contact DR Support at firstname.lastname@example.org or via support-chat on the bottom right.