Function Menu Bar & Query Editor

The Function Menu Bar in the Query Sheet is a command center for efficiently managing data tasks. It simplifies functions such as filtering, aggregating, and sorting. The Query Sheet offers two modes, Auto SQL and Advanced SQL, tailored to different skill levels and needs.

Auto SQL

Auto SQL mode is perfect for users who may not be SQL experts but need to work with data in the Query Sheet. OvalEdge has created a user-friendly toolbox called the Function Bar within this mode. It is a versatile set of tools that allows users to perform various data operations, like filtering, joining, grouping, and more. The following sections will explain each function in detail.

Functions supported in Auto SQL Editor

• Filter Filtering enables the selection of specific rows in the data based on defined rules. It involves using a filter to extract rows from a table with particular words or patterns.

  
• GroupBy Users with data write permissions can use the grouping function to remove duplicates from selected columns.

  
• Aggregate Aggregate functions perform calculations such as Average, Min, Max, etc., on grouped values and provide a single result. These functions return consistent results for specific input sets, similar to the "HAVING CLAUSE" in SQL Queries. Available functions include:

  • Avg: Calculates the average value of the column.

  • Count: Returns the count of data points (cells with data) in the column.

  • Max: Calculates and returns the maximum value from the column.

  • Min: Calculates and returns the minimum value from the column.

  • Sum: Calculates the sum of values in the column.

    
• Ascending Sort The Ascending Sort option arranges column values in ascending order. If the column contains alphabets, it sorts them from A-Z. For numerical values, it sorts from 1-9. If the column has alphabets mixed with numbers, it sorts the numbers first and then the alphabets. For example, if the values are 1A, A1, B2, 1B, 2B, after applying ascending sort, the result will be: 1A, 1B, 2B, A1, B2. If no specific column is selected, the first column is automatically sorted.

  
• Descending Sort Descending Sort arranges column values in descending order. If no column is selected, it defaults to sorting the first column.

  
• Original Sort When we crawl the data from a remote system, the Original Sort feature arranges the data in the same order and sorts it as it did in the remote system.

  
• Join It recommends matching columns between tables for joins, employing machine learning algorithms. We can manually join columns as required.

  
• Union Union combines data by appending rows from one table to another. For instance, to add new Customer IDs from one table to an existing list of Customer IDs in another table, ensure both tables have the same number of columns, column names, and data types. To union two tables on a matching field manually: Use this feature to perform a manual union on two tables with matching fields. It creates a table containing all rows from both tables with some null values.

  
• Rename Column Name This enables renaming a column. This will rename the column in the OvalEdge table.

  

Manage Table Columns

This option is available in the middle right in Auto SQL mode. Users can show or hide a column in the result section according to their preferences. By default, “Select All” will be checked. Users can also select only the important columns that they want to see in the executed query result. Users can quickly examine the columns by clicking the ‘Eye’ icon before every column.

Advanced SQL

Advanced SQL mode enhances SQL proficiency and grants more significant control over queries. It allows them to execute complex queries and achieve precise results using their SQL skills. Queries that can be executed may vary depending on the connector. For example, a user can execute DDL & DML queries like SELECT, INSERT, TRUNCATE, etc., for the connector based on SQL. (Note: The execution of queries also depends upon the permission provided in the source system.)

Collaborate on Query

Team members can collaborate on a query using the "Conversations" button. This functionality allows multiple users to collaborate on a query, share insights, and contribute to analysis seamlessly. It promotes teamwork and improves the efficiency of query tasks, making collaborative data analysis straightforward.

Query Sheet Options (9-Dots)

New Queries

New queries within the selected schema can be created, enhancing data analysis. This feature is exclusive to Advanced SQL mode, which is ideal for those requiring precise query control for in-depth data analysis.

Query History

The query editor saves every query as a historical query. Access and save the test query using the "Catalog" button. They can also check the results of past queries by clicking the "View Result" icon on the same page. Users can also edit the name of the query executed as needed, and the same name will be reflected in the Data Catalog. Exploring the collaboration history of a specific query is possible from the "History" tab. This makes it simple to manage and review their queries and outcomes.

Cataloged Query

All saved queries and their status appear in the cataloged query window. Clicking on the code name allows direct access to the query summary page in the Data Catalog. The cataloged queries are saved in the Data Catalog permanently.