Dynamically Creating, Visualizing, and Modifying SQL Statements

Andi's Dynamic SQL Creator and Dynamic SQL Visualizer empower users of all skill levels to build and parse intelligent SQL Queries. This allows novice SQL users to quickly create, visualize, and modify statements without reliance on more Technical users. More experienced SQL users can eliminate wasted time and re-work spent writing lengthy and complex statements.

Both the Dynamic SQL Creator and the Dynamic SQL Visualizer can be found in the Tools menu:

Open

The Dynamic SQL Creator allows users to visually design SQL Queries through an intuitive and interactive visual interface.

To get started, select the Dynamic SQL Creator from the Tools menu:

Open

The Dynamic SQL Creator will open to begin dynamically and visually creating a new SQL statement:

Open

The Dynamic SQL Visualizer can parse and reverse engineer SQL Queries of any complexity through a modifiable visual diagram. This allows for a more thorough visual understanding of complex SQL statements, and an error proof method to modify these complex statements as well. Even users with limited SQL experience can quickly dive in to understand more complex Queries.

There are visualizer features, such as the Join Table icon, which require SQL statements to be written as intelligent SQL Queries. You can however continue to manually join tables by clicking on a column and dragging it to the target table and dropping on the target table column which completes the join.

To get started, highlight your SQL statement from the Query Editor and select the Dynamic SQL Visualizer from the Tools menu:

Open

The SQL statement will reverse engineer into a simpler, user friendly way of viewing. The available tools can be used to modify the statement as needed, without writing SQL!

Open

The Dynamic SQL Creator and Dynamic SQL Visualizer are broken out into several key areas. This includes the SQL Attributes Tree, the Visual Designer, the SQL Modifiers, Table Selection, and the SQL Select Statement Editor.

• Table Selection

• Visual Designer

• SQL Attributes Tree

• SQL Modifiers

• SQL Select Statement Editor

• Supported Databases

Tables available to add to the Visual Designer are listed on the right hand side of both the Dynamic SQL Creator and the Dynamic SQL Visualizer. The Schema and it's associated Tables will be listed in alphabetical order:

Open

To begin, simply drag and drop a Table onto the Visual Designer, or double click on a Table to automatically add it to the Visual Designer. The Table Elements will appear with the associated Table Columns to select from to add to your SQL Statement:

Open

The Visual Designer allows you to create, visualize, and modify your SQL Statements. After adding a Table(s) to the Visual Designer you can begin selecting Table Columns using the indicators to add them to your Statement:

Open

When Referential Integrity exists within the database, the Join Table icon will be located in the upper right corner of the Table Elements. By selecting it, the associated Tables that can be joined will appear:

Open

The Join Table icon can also be used to create multiple Joins across Tables: Note: The blue and green arrows that display will inform you which Table Element owns the Primary Key.

Open

You can double click the Join Link between Tables to view the Link Properties and make adjustments to the Join: 

Open

It is important to understand the intelligent SQL Query format needed to fully qualify the Column names when using the SQL Visualizer to join Tables. As an example, the following SQL format would not allow a user to utilize the Join Link icons to dynamically Join tables (it would require a manual drag-and-drop Join from one column Table to another). This is because the Column names have NOT been fully qualified:

Open

Open

However, when the SQL has fully qualified Column names, the Visual Designer can be used to dynamically create Joins using the Join Link:

Open

Open

If your database does not have Referential Integrity, joining tables will work differently. When attempting to join a Table using the Join Link you will see that there are "No linked objects" available to select:

Open

To join a Table when Referential Integrity does not exist, you will need to understand the database Table relationships. You can then Join two Tables by left mouse clicking on the desired Column within the Table, holding the left mouse click down, and then releasing the mouse click on the Column in the other Table:

Open

The SQL Select Statement Editor will dynamically show the updated SQL with your Join:

Open

By right mouse clicking in any open space of the Visual Designer additional options will appear to continue uniquely modifying your SQL Statement.  

Open

By double clicking (left mouse click) in any open space of the Visual Designer additional Query Property options will appear, which may include database specifics options:

Open

The SQL Attributes Tree is located in the upper left corner of the Dynamic SQL Creator and the Dynamic SQL Visualizer. It will dynamically display the fields in use for the statement as well as the Tables that the fields are pulling from as you interact with the Visual Designer. Different sections of the Tree can be expanded or minimized depending on the need to see them:

Open

As new Tables and Fields are added to the Visual Designer they will dynamically appear in the SQL Attributes Tree:

Open

To create a Table Alias, double click on any of the Tables in use within the SQL Attributes Tree and enter the new Alias into the text field:

Open

The SQL Modifiers are used to dynamically modify your SQL Statement with Aggregates, Aliases, Sort Types, and more! Every column that is selected from a Table Element will automatically add a row as a SQL Expression which can be dynamically modified:

Open

Select an Output indicator next to an Expression to remove or add a Column to the SQL Statement. This is the same as selecting or deselecting an Indicator field from a Table Element Column. 

The Expression allows users to define which Table Column is to be used for that particular row Expression. By changing an Expression from the menu, it will automatically select the new Indicator field from the correct Table Element while deselecting the former Indicator from the Table Element. This ensures the Table Elements and the SQL Modifiers dynamically stay in sync. 

Open

For every Expression displayed an Aggregate function can be selected from the associated row drop down menu. 

Open

An Alias can be used for any of the Expressions by simply clicking in the Alias text field and entering in the value desired.

Open

A sort can be placed on the Expression in either Ascending or Descending order by using the Sort Type menu: 

Open

The Sort Order column allows users to define which order that the Expression should appear in the result set.

Open

Note: If you have already selected a Sort Type for an Expression (Ascending or Descending) as you fill in the Sort Order, the Sort Type will default automatically to what was set for the first Expression. 

Create a Grouping (Group By) by selecting the Indicator for the desired Expression.

Open

Note: If a Grouping Indicator is selected, the Criteria for column will automatically fill in for all Expression rows with the default "For values" 

The Criteria for column can be used to define if Criteria should be used for specific Values or for Groups. 

Open

Criteria can be added to Expressions to act as a dynamic "Where" clause for your SQL statement. For example, by typing NURSING into the Criteria column for ANDI.CLASS.CLASS_NAME it will automatically format the Criteria to = 'NURSING' for the correct SQL syntax. The same goes for ANDI.SCHOOL.NAME, where M% was entered, and automatically formatted to Like 'M%':

Open

Additional criteria can be added to an Expression using the 'Or...' columns which will dynamically create additional OR statements for your statement.

Open

Open

The SQL Select Statement Editor (located at the bottom of both the SQL Creator and SQL Visualizer) will dynamically update with your created SQL Statement as you create and modify it:

Open

You can also type directly into the SQL Select Statement Editor to manually edit a statement, as desired.

The following databases are currently supported, other databases may be added in the future.

• Advantage

• DB2

• Firebird

• Informix

• MySQL

• Netezza

• Oracle

• PostgreSQL

• SQLite

• SQL Server

• Sybase

• Teradata