SQL Editor Overview

The SQL Editor helps users write, edit, and execute SQL (Structured Query Language) queries within Optilogic’s platform. It provides direct access to database objects such as tables and views stored within the platform. In this documentation, the Anura Supply Chain Model Database (Cosmic Frog’s database) will be used as the database example.

Anura model exploration and editing are enabled through the three windows of the SQL Editor:

1. Database Browser: Explore and select from your databases.
2. Query Editor: Construct & execute SQL queries and view results.
3. Metadata Explorer: Access table structure and query information.

The Anura database is stored in PostgreSQL and exclusively supports PostgreSQL query statements to ensure optimized performance. Visit https://www.postgresql.org/ for more detailed information.

To enable the SQL editor, select a table or view from a database. Once selected, the SQL Editor will prepopulate a Select query, and the Metadata Explorer displays the table schema to enable initial data exploration.

Database Browser

The Database Browser offers several tools to explore your databases and display key information.

1. Search Bar: Find databases based on keyword search and extend the keyword phrase to limit results.
2. Collapse Databases: Hides all exposed database objects, leaving a list of available databases.
3. Custom Items: Limit displayed results to user-modified schema objects (models, tables, views).
4. Show/Hide Empty: Toggle to include or exclude empty schema objects (tables, views).
5. Refresh: Update the list of available schema objects (databases, tables, views).
6. Database: Anura (Cosmic Frog) model.
7. Table: Data table stored within a Cosmic Frog model. Note: The row count & schema modification icons appear to the right of table names.
8. View: A virtual table generated from one or more tables to present a customized subset of data by executing a SQL query.

Query Editor

The Query Editor enables users to create and execute custom SQL queries and view the results.  Reserved words are highlighted in blue to assist in SQL editing.  This window is not enabled until a model table or view has been selected from the database browser; once selected, the user is able to customize this query to run in the context of the selected database.

1. Builder: Filter table or view data by adding additional query logic to the where clause.
2. Format: Parses the current SQL statement inserting line breaks and indentations, making it easier to follow query logic.
3. Replace: Removes all query text and replaces with the currently stored clipboard data (Ctrl+v).
4. Bookmark: Add the current query to a metadata library stored at the database level
5. Clear: Removes all query text.
6. Execute: Execute the enabled text within the query editor. Note: To disable a single line, add the prefix — to the statement line. To disable multiple lines, add the prefix /* to the first line and the suffix */ to the last line.
7. Export Results: Exports the results from the executed query to CSV or XLS.

Metadata Explorer

The Metadata Explorer provides a set of tools to efficiently create and store SQL queries.

1. Data Types: Explore the complete list of column names with data types of the selected table or view.
2. Bookmark: Access queries that have been previously saved within the database.
3. Suggestions: Browse a list of template queries to accelerate your query creation.
4. History: View the session history of executed queries.

SQL Query Basics

SQL is a powerful language that allows you to manipulate and transform tabular data.  The query basics overview will help guide you through creating basic SQL queries.

Example 1: Filter Criteria - Customers with status set to include without latitude
SELECT A.CustomerName, A.Status, A.Region
FROM customers A
Where A.Latitude IS NOT NULL and A.Status = ‘Include’

Example 2: Summarizing Records - Regions with 2 or more geocoded customers
SELECT A.Region, A.Status, Count(*) AS Cnt
FROM customers A
Where A.Latitude IS NOT NULL
Group By A.Region, A.Status
Having Count(*) > 1
Order by Cnt Desc

Table Joins

Often, your model analysis will require you to use data stored in more than one table. To include multiple tables in a single SQL query, you will have to use table joins to list the tables and their relationships.

If you are unsure if all joined values are present in both tables, leverage a Left or Right join to ensure you don’t unintentionally exclude records.

Example 1: Inner Join - Join Customer Demand and Customers to add Region to Demand
SELECT A.CustomerName, A.ProductName, B.Region, A.Quantity
FROM customerdemand A INNER JOIN Customers B
  on A.CustomerName = B.CustomerName

Example 2: Left Join - Find Customer Demand records missing Customer record
SELECT A.CustomerName, A.ProductName, B.Region, A.Quantity
FROM customerdemand A Left JOIN Customers B
  on A.CustomerName = B.CustomerName
Where B.CustomerName is Null

Example 3: Inner Join & Aggregation – Summarize Demand by Region
SELECT B.Region, A.ProductName, SUM(Cast (A.Quantity as Int)) Quantity
FROM customerdemand A INNER JOIN Customers B
  on A.CustomerName = B.CustomerName
Group By B.Region, A.ProductName

Table Union

When data is separated into two or more tables due to categorical differences in the data, a join won’t work because there is a common structure, not a relationship between the tables. A UNION is a type of join that allows you to merge the results of two separate table queries into a single unified output. Ensure each query has the same number of columns in the same order.

Example 1: UNION – Create a unified view of all customers and facilities that are geocoded
SELECT A.CustomerName as SiteName, A.City, A.Region, A.Country, A.Latitude, A.Longitude, 'Cust' as Type
FROM customers A
  UNION
SELECT B.FacilityName as SiteName, B.City, B.Region, B.Country,B.Latitude, B.Longitude, 'Facility' as Type
FROM Facilities B

Sub-Query

As queries grow in complexity, it is often easiest to reset the table references by creating a sub-query.  A sub-query allows you to create a new virtual table and reference this abbreviated name and structure as you build out a query in phases.

Example 1: Subquery +UNION – Create a unified view of all customers and facilities that are geocoded
SELECT C.SiteName, C.city, C.Region, C.Country, C.Latitude, C.Longitude, C.Type
FROM (
  SELECT A.CustomerName as SiteName, A.City, A.Region, A.Country, A.Latitude, A.Longitude, 'Cust' as Type
  FROM customers A
    UNION
  SELECT B.FacilityName as SiteName, B.City, B.Region, B.Country,B.Latitude, B.Longitude, 'Facility' as Type
  FROM Facilities B
) C
WHERE C.Latitude IS NOT NULL

Table Search Filter

As data tables grow, it is often more efficient to use a table filter to find missing values than a left join and null filter criteria.

Example 1: Table Search Filter – CustomerDemand without a Customer match
SELECT * FROM customerdemand A
WHERE NOT EXISTS (SELECT B.CustomerName FROM Customers B WHERE A.CustomerName = B.CustomerName)

Deploying Queries in Cosmic Frog Analytics

The Analytics module in Cosmic Frog allows you to display data from tables and views. Custom queries can be stored as views, enabling the analytics module to reference this virtual table to display results.  Creating a view follows a very similar query construct as a sub-query, but rather than layering in a select statement, you add CREATE VIEW viewname as ( query).

Once created, a view can be selected with the Analytics module of Cosmic Frog.

 

Example 1: Create View – Creating an all-site view
CREATE VIEW V_All_Sites as
(
  SELECT C.SiteName, C.city, C.Region, C.Country, C.Latitude, C.Longitude, C.Type
  FROM (
    SELECT A.CustomerName as SiteName, A.City, A.Region, A.Country, A.Latitude, A.Longitude, 'Cst' as Type
    FROM customers A
      UNION
    SELECT B.FacilityName as SiteName, B.City, B.Region, B.Country,B.Latitude, B.Longitude, 'Fac' as Type
    FROM Facilities B
  ) C
)

Example 2: Delete View – Delete V_all_sites view
Drop VIEW v_all_sites

Altering Tables

SQL queries can also modify the contents and structure of data tables. This is a powerful capability, and the results, if improperly applied, cannot be undone.

Table updates & modifications can be completed within Cosmic Frog, with the added benefit of the context of allowed column values. This can also be done within the SQL editor by executing UPDATE and ALTER TABLE SQL statements.

Example 1: Modifying Tables – Adding Additional Notes Columns
ALTER TABLE Customers
ADD Notes_1 character varying (250)

Example 2: Modifying Values – Updating Notes Columns
UPDATE Customers
SET Notes_1 = CONCAT(Country , '-' , Region)

Example 3: Modifying Tables – Delete New Notes Columns
ALTER TABLE Customers
DROP COLUMN Notes_1

Example 4: Copying Tables – Copy Customers Table
SELECT *
INTO Customers_1
FROM Customers

Example 5: Deleting Tables – Delete Customers Table
DROP TABLE Customers_1

Visit https://www.postgresqltutorial.com/ for more information on PostgreSQL query syntax.