System and method of query transformation

• US 20050010569A1
• Filed: 05/27/2004
• Published: 01/13/2005
• Est. Priority Date: 05/27/2003
• Status: Abandoned Application

• Current Assignee: International Business Machines Corporation
• Original Assignee: Cognos Incorporated (International Business Machines Corporation)
• Inventors: Styles, Michael E.
• Application Number:
  US10/856,747
• Publication Number:
  US 20050010569A1
• Time in Patent Office: Days
• US Class Current: 1/1
• CPC Class Codes: G06F 16/244 Grouping and aggregation
• CPC Class Codes: G06F 16/24547 Optimisations to support specific applications; Extensibility of optimisers
• CPC Class Codes: G06F 16/283 Multi-dimensional databases or data warehouses, e.g. MOLAP or ROLAP
• CPC Class Codes: Y10S 707/954 Relational
• CPC Class Codes: Y10S 707/99931 Database or file accessing
• CPC Class Codes: Y10S 707/99933 Query processing, i.e. searching
• CPC Class Codes: Y10S 707/99934 Query formulation, input preparation, or translation
• CPC Class Codes: Y10S 707/99935 Query augmenting and refining, e.g. inexact access

Abstract

A group query transformation system for Transforming structured query language group query windowed aggregates is provided. The group query transformation system comprises a group query analysis module for analyzing one or more structured query language (SQL)/on-line analytical programming (OLAP) windowed aggregates that are not supported by a target database system, and a group query transformation module for transforming the one or more SQL/OLAP windowed aggregates into semantically equivalent standard aggregate functions that are supported by the target database system.

Claims

1. A group query transformation system for transforming structured query language group query windowed aggregates, the system comprising:

a group query analysis module for analyzing one or more structured query language (SQL)/on-line analytical programming (OLAP) windowed aggregates that are not supported by a target database system; and

a group query transformation module for transforming the one or more SQL/OLAP windowed aggregates into semantically equivalent standard aggregate functions that are supported by the target database system.

2. A method of transforming structured query language (SQL) group query windowed aggregates, the method comprising the steps of;

analyzing one or more SQL/on-line analytical programming (OLAP) windowed aggregates that are not supported by a target database system; and

transforming the one or more SQL/OLAP windowed aggregates into semantically equivalent standard aggregate functions that are supported by the target database system.

3. The method as claimed in claim 2, wherein the step of analyzing includes the steps of:

determining if a transformation is to be performed; and

determining the number of derived tables required based upon the number of control breaks.

4. The method as claimed in claim 3, wherein the step of determining if a transformation is to be performed comprises the steps of:

assigning a group index; and

tracking control breaks.

5. The method as claimed in claim 2, wherein the step of transforming includes the steps of:

generating the derived tables;

adding grouping columns to derived table select lists;

generating a WHERE clause based upon control breaks and nullability of grouping columns;

traversing a group query select list; and

converting and moving candidate aggregates into the derived table select lists.

6. The method as claimed in claim 5, wherein the WHERE clause is of the form

T1.C1=T2.C1 or (T1.C₁ IS NULL and T2.C1 IS NULL) AND . . . if grouping columns are nullable.

7. A method of group query transformation, the method comprising the steps of:

determining if a detailed table is required;

traversing an aggregate list and assigning a group index to each aggregate based on the location of a control break in a control break list;

determining a derail table T_n;

traversing the control break list and for each control break in the control break list;

adding all columns in the control break to a select list of a derived table; and

for each column in the control break, constructing a join condition of the form T_n.C_j-T₁.C_j OR (T_n.C_j IS NULL AND T_i.C_j IS NULL);

traversing the select list and analyzing each expression and performing a specific action based on the type of expression encountered; and

constructing a final query.

8. A computer data signal embodied in a carrier wave and representing sequences of instructions which, when executed by a processor, cause the processor to perform a method of transforming structured query language (SQL) group query windowed aggregates, the method comprising the steps of:

analyzing one or more SQL/on-line analytical programming (OLAP) windowed aggregates that are not supported by a target database system; and

transforming the one or more SQL/OLAP windowed aggregates into semantically equivalent standard aggregate functions that are supported by the target database system.

9. A computer-readable medium having computer readable code embodied therein for use in the execution in a computer of a method of transforming structured query language (SQL) group query windowed aggregates, the method comprising the steps of:

analyzing one or more SQL/on-line analytical programming (OLAP) windowed aggregates that are not supported by a target database system; and

transforming the one or more SQL/OLAP windowed aggregates into semantically equivalent standard aggregate functions that are supported by the target database system.

10. A computer program product for use in the execution in a computer of a group query transformation system for transforming structured query language (SQL) group windowed aggregates, the computer program product comprising:

a group query analysis module for analyzing one or more SQL/on-line analytical programming (OLAP) windowed aggregates that are not supported by a target database system; and

a group query transformation module for transforming the one or more SQL/OLAP windowed aggregates into semantically equivalent standard aggregate functions that are supported by the target database system.