Query Designer

Graphical Query Designer has following advantages that significantly reduce time developing and testing complex SQL SELECT statements against selected database tables and views: :

automatically generates joins between selected tables based on existing referential integrity constraints
visualizes in a diagram view joined table relationships as well as sorting, filtering and grouping criteria defined in a query
allows to use drag-and-drop to define column joins and to choose columns for query result set
provides expression builder to build complex expressions in WHERE clause
allows to create sorting and grouping criteria in a query based on existing list of columns and expressions
provides columns datatypes lookup and verification of query parameters
catches ‘cartesian product’ situations
allows in-place query execution and data result display

Use can open Query Designer as a standalone document in Server Studio or invoke it from any opened SQL Editor document to paste generated SQL SELECT statement into existing editor’s script.

To open Query Designer as a standalone document, choose Tools -> SQL Editor -> Query Designer top-level menu.

To open Query Designer from SQL Editor, use Query Designer from editor’s popup menu.

Query Designer consists of Object Selector dialog box and Designer document panel.

Object Selector dialog box allows to select tables and views to include into a query.

Designer document panel consists of Design and SQL views that allow joining selected tables, choosing columns for a query, adding filter, sorting and grouping conditions. Generated query can be executed to see result output, which it produces. You can also just preview execution plan of a generated query without actually executing it.

Object Selector Dialog Box

Object Selector Dialog Box always opens when you invoke Query Designer to allow choosing initial list of objects - tables and views - to use in the query. Selected tables will be included in FROM clause of generated SQL SELECT statement. You can open this dialog box later to add other tables.

To open this dialog box again after initial designer main panel is opened, use Add Table/View menu in the diagram popup menu or press Add button in FROM tab of Design view.

When selecting tables and views, you can optionally provide alias names for them. If you do not provide aliases initially, you still can add them later in FROM tab of Design view of the Query Designer main panel.

Query Designer Main Panel

Query Designer Main Panel consists of two views: Design and SQL. These views can be switched using tabs on top of the Designer main panel or Query Designer -> View top-level menu.

Design View allows you to create and edit definitions of your query using visual tools.

SQL View allows you to see SQL SELECT statement that was generated based on your selections in Design view and also to see data results produced when you execute generated query.

Design View

Design View consists of the diagram area at the top of the panel and design tabs at the bottom.

Diagram Area

Diagram Area displays tables and views in the query and links between them that represent column joins. It allows joining columns in tables by using drag-and-drop. To join two columns, left click on column in a table and drag it to another column that you want it to join. Once the join is created, it will also be added to a grid in WHERE tab of the design area. You can right click on the connector line to open popup menu or double-click on connector line to open Join Properties dialog box. It allows you to choose a join type: INNER or OUTER and also displays columns participating in the join. Use popup menu on a join connector line to delete a selected join.

You can also use the diagram to select columns in SELECT list of the query by checking on checkbox located left to a column name.

There are three icon indicators that might be displayed right to a column name in the diagram’s table object: filter, sort, group indicators. Filter indicator is displayed when there is additional filter criteria (other than join criteria) defined for this column in WHERE clause. Sort icon is displayed when there is sort criteria defined for this column in ORDER BY clause. Group indicator is displayed when this column participates in GROUP BY clause.

You can optionally change the diagram view to display column datatypes along with column names. Use Show Column Type menu in the diagram popup menu.

Double-click on table object or use Properties popup menu on a table object to open table properties dialog box that lists column names and datatypes. You can also use table properties dialog box to set table alias.

Design tabs

There are six tabs in Design area: SELECT, FROM, WHERE, ORDER BY, GROUP BY, HAVING. These tabs represent corresponding clauses of SQL SELECT statement.

SELECT tab has a grid with list of columns to display as a query output. You can optionally specify alias for each selected column by typing it in the grid. You can pick columns for display from dropdown list located in the Column/Expression cell of the grid and also use menu in the same dropdown list to open Expression Builder dialog box. Expression Builder allows you to build expressions using Informix built-in and aggregate functions, operands and all table columns available in the query.

In top area of SELECT tab you can define DISTINCT and FIRST caluses of the SQL SELECT statement. Check DISTINCT checkbox to suppress repeated rows in a query result set. Check FIRST checkbox and enter number of rows to retrieve if you want to limit number of retrieved rows.

Use Move Up and Move Down buttons to change of columns in the SELECT list.

FROM tab contains list of tables and views that are part of the query. You can use this tab to specify table aliases and also to add new tables and/or views to a query.

WHERE tab allows you to edit filter and join conditions of the query. Join conditions are usually edited using drag-and-drop operation in the diagram area and also created automatically based on selected tables referential integrity constrains defined in the database. But you can also define joins directly in the grid using provided dropdown lists in Column/Expression cells and Join checkbox. To define a join select two columns that you want to join and select operand “=” between them in Operation cell. Check Join checkbox in a first cell of the grid.

To define additional filter criteria, which is not a join, select column or expression in left Column/Expression cell, select required operand, like “>” or “=” in Operation cell and enter constant value in right Column/Expression cell. If operand is “IN” or “LIKE” you can type list of values in right Column/Expression cell exactly like they have to appear in WHERE clause.

For example, if you have column ‘lastname’ and you want to define criteria:

WHERE lastname IN ‘Green’,’Smith’,’Kelly’

select column ‘lastname’ from a dropdownlist in left Column/Expression cell, select operand IN from operation dropdown list and type ‘Green’,’Smith’,Kelly’ in the right Column/Expression cell. Press Add button to add a new filter criteria if you want to define multiple filter criteria.

By default, the grid uses AND logical operation between multiple filter conditions but you can explicitly set AND or OR logical operation using last Logical cell in the grid. You can also group filter conditions in AND or OR groups as needed using AND/OR with parentheses that are available in Logical dropdown list.

For example, to generate the following condition:

WHERE (type = 10 and operation=”Sell”) OR type = 26

add following three rows in the WHERE tab grid:

type = 10 AND
operation = Sell ) OR
type = 26

When there is a mix of join conditions and other filter conditions, SQL is always generated the following way: All join conditions have AND logical operation between them and they are automatically surrounded by parentheses. All other filter criteria are also automatically surrounded by parentheses. There is AND logical operation between joins and other filter criteria.

For example, when there are 3 tables in FROM clause - T1, T2 and T3 - and they are joined using the following criteria:

T1.col1=T2.col2 and T2.col3=T3.col5

and also there are 2 additional filters for T1 table:

T1.col6 = “Green” or T1.col6=”Brown”

SQL will be generated in the following form:

WHERE (T1.col1=T2.col2 and T2.col3=T3.col5) AND (T1.col6 = “Green” OR T1.col6=”Brown”)

ORDER BY tab is used to define sorting criteria. Press Add button to add sort criteria to the grid, select a column or expression in Column/Expression cell and optionally specify sorting direction (ASC or DESC) using provided Order cell. Default sorting direction is ascending (ASC).

Use Move Up and Move Down buttons to change the order of sort columns in ORDER BY clause.

GROUP BY tab is used to define grouping criteria. Press Add button to add grouping criteria to the grid, choose a column or create expression in Column/Expression cell. Notice that only those columns that are currently selected in SELECT list of the query are available in dropdown list to choose from for GROUP BY operation.

HAVING TO tab allows you to define filters for groups. This tab is enabled only where there are GROUP BY criteria defined for a query. It works the same way as WHERE tab but generated filters are added to HAVING TO clause of SQL SELECT statement and apply to defined groups rather than to individual query result records.

SQL View

SQL view is split into two areas.

Top area displays generated SQL SELECT statement in read only mode.

Bottom area displays query results after this query is executed or execution plan of the query.

Using top area you can copy and paste entire SQL select statement or portion of it and also if there is syntax error in syntax, it highlight exact position of the error in SQL statement.

You can transfer entire SQL statement into new SQL Editor panel or new Benchmark Runner panel using popup menu in editor area or top-level toolbar buttons.

To execute generated SQL query, press Execute button in the toolbar or choose Execute from popup menu. If there is no syntax error, the query will be executed and results will be displayed in the results grid at the bottom.

To view execution plan of generated SQL query without executing it, press Execution Plan button in the toolbar or choose Execution Plan from popup menu. If there is no syntax error, the execution plan diagram will be displayed at the bottom of the panel.

You can also save generated SQL query in a text file. To save SQL in a text file choose File -> Save menu.