Browse Prior Art Database

Unifying Database Query Results to Improve Reporting Accuracy in a Highly Complex and Dynamic Information Warehouse Reporting Environment Disclosure Number: IPCOM000016318D
Original Publication Date: 2002-Oct-12
Included in the Prior Art Database: 2003-Jun-21

Publishing Venue



Disclosed is a automated process for combining the result sets from multiple database queries into a single relational table, in order to improve information warehouse reporting accuracy. The database queries are high complex and change on a daily basis. Each query represents a functional business entity view of the data. Combining the query result sets enables gaps and overlaps between queries to be easily identified and corrected. The database queries are written by multiple knowledgeable end users in Structured Query Language (SQL) and maintained using the Query Management Facility (QMF). A relational DB2 table is used to identify the queries within QMF and associate each query with a business entity such as a business unit executive name. This DB2 table is used to drive the automated process. The automated process begins by extracting the WHERE clause from each end user query, in order to construct a new query which uses a common SELECT and FROM clause. The use of a common SELECT and FROM clause ensures that the query result sets are in a consistent format. The queries are then executed and each result set is stored in a file. The result set files are then combined and loaded into a DB2 table, in order to run a query to identify gaps in the result sets. The result set from this gap query is then added to the same DB2 table containing the combined result sets. Overlaps in the result sets are identified using SQL within a DB2 view. Error handling is incorporated within the process to allow the process to continue in the event that a end user query has been written incorrectly. A graphical overview of the process is as follows: