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

• Join tables from different DBMS, for example, Oracle and Sybase. All you need is to first query data from Oracle and create a report table into Sybase, and then join the tables in Sybase for the further query.
• Rather than directly run a query in a main database (e.g. Oracle) processing requests for multi-users, transfer the most frequently used data from the main database to your local database (e.g. Access) in which query will be executed more efficiently

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:

• Categorize different occupations on a single Occupation field
• Group specific time ranges on a single Date field
• Differentiate the attributes of product stock data on a single ProductName field

• Overall conditions - the conditions for retrieving data to the report table
• Row conditions - the conditions for retrieving data to certain rows in the report table
• Column conditions - the conditions for retrieving data to a certain column in the report table

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

• Reduce query creating time by simply clicking relevant conditions for selected tables and columns
• Reduce query testing time by avoiding data type errors via preview facilities
• Simplify multi-step query work by performing one-step multi-dimension query
• Reduce query executing time significantly by exporting initial query results from a main database into a local database, which makes it possible to flexibly separate complicated queries and execute time-consuming parts in the local database.

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

• Preview table structures for all selected database tables
• Preview data samples for all selected database tables
• Create dynamic SQL statements without any programming
• Create query report tables into any specified DBMS
• Manage created query objects in a QueryShell library for reuse

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:

C99MAIN 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)

C99ASSIT AreaID (Area code. Text, 8 digits) Pop_Chin (Chinese population, Numerical) AvgHHInc (Average household income, Numerical) AvgDwVal (Average dwelling value, Numerical)

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
BC
ONT
QUC
CAN

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!

Price