An advanced database query tool

As necessary to perform ordinary query tasks, QueryShell can help generate complex queries without any programming for all ODBC-connectable DBMS such as DB2, Oracle, Sybase, SQL Server, Informix, Access, etc.

In advance of other query tools currently available on the market, QueryShell supports to generate a query report table into a different DBMS, which can be directly used for further queries. QueryShell enables setting up multi-dimension conditions (e.g. overall conditions, row conditions and column conditions) for a complex query task to generate its query result report in one step. Therefore, no efforts on manually combining several search results from individual SQL query sets will ever be required. Easy-use features in QueryShell include the preview of data samples and database table structures.

Report query results across different DBMS

There may be more than one DBMS involved in your business process. For example, you may want to transfer data from a DB2 database to an Oracle database. Normally you will need to query data from the former and get the results in ASCII files, and then import the ASCII files into the later. It could be very time consuming if large amounts of data are involved. Using QueryShell, this operation can be easily performed.

In QueryShell, query results from a DBMS can be directly transferred to any other DBMS without ASCII file export/import operations. As the applications of this feature, you can

Multi-dimension query

Gathering and categorizing data against multiple criteria will be often involved in your daily query tasks. Traditionally, a set of SQL queries has to be created and executed one by one in order to obtain the result categorized on a single row/column in a table. Then the results are manually combined. The followings are some typical examples required in daily reports:

Using QueryShell, the same requirement can be fulfilled in one step by setting up multi-dimension conditions, which include Efficient and Flexible

Using QueryShell, queries can be created and executed most efficiently as you will be able to

Easy to use

Using QueryShell, queries can be much more easily created and executed as you will be able to

Example of Complex Query

Click on the logo to get a full image.

Suppose there are two tables containing census data. The fields in each table are illustrated by the following figure:

AreaID (Area code. Text, 8 digits)
FSA (First 3 digits of post code)
Ttlpop (Area population, Numerical)
TtlMal (Area male, Numerical)
TtlFem (Area female, Numerical)
Hhlds (Area households, Numerical)
AreaID (Area code. Text, 8 digits)
Pop_Chin (Chinese population, Numerical)
AvgHHInc (Average household income,
AvgDwVal (Average dwelling value,
Two tables are joined each other with the field AreaID.
Now you are asked to create following summary table based on the above data:

Prov All Area Urban Area Rural Area
P o p Hhlds HHInc P o p Hhlds HHInc P o p Hhlds HHInc

This query has to employ three-dimensional conditions:

For whole query
Join condition: C99Main.AreaID= C99Assit.AreaID

For Rows
Row "BC": Left(C99Main.AreaID,2)='59' or C99Main.AreaID LIKE '59%'
Row "ONT": Left(C99Main.AreaID,2)='35' or C99Main.AreaID LIKE '35%'
Row "QUC": Left(C99Main.AreaID,2)='24' or C99Main.AreaID LIKE '24%'
Row "CAN": No area condition

For Columns
Cloumns under "All Area": No area condition
Columns under "Rural": Mid(C99Main.FSA,2,1)='0' or C99Main.FSA LIKE '_0%'
Columns under "Urban": Mid(C99Main.FSA,2,1)<>'0' or NOT C99Main.FSA LIKE '_0%'
(For Column "Pop" and "Chinese" the formula "ALL" - "Rural" can be used to save the total query time)

Each column has to employ an aggregation function
Column "Population": SUM(C99Main.Ttlpop)
Column "Hhlds": SUM(C99Assit.Hhlds)
Column "Income": AVG(C99Assit.AvgHHInc)

When using normal SQL to accomplish the query, in total 4 X 9 = 36 SQL statements have to be created (If the above column formula is used, the SQL statement number can be dropped down to 4 X 7 = 28, still a time-consuming task).

To finish this kind of query, a senior programmer may have to spend about one hour to enter and debug the SQL statements. A junior programmer might have to take two hours or more to accomplish the same task. The difficulty for junior programmers is not only in writing SQL statements, but also in how to organize the individual SQL results into one table.

By using QueryShell, both senior and junior programmers can expect to finish the query definition within twenty minutes! As the query's complexity increases, the benefits of using QueryShell will be even more significant. This level of efficiency would be cost-effective to everyone who accesses multiple differential databases. QueryShell can make its cost back within DAYS!