System and method of query transformation

• US 20090063441A1
• Filed: 09/09/2008
• Published: 03/05/2009
• Est. Priority Date: 05/27/2003
• Status: Active Grant

• Current Assignee: SAP SE
• Original Assignee: SAP AG (SAP SE)
• Inventors: Styles, Michael E.
• Granted Patent: US 8,108,415 B2
• 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 system for transforming client/server aggregates is provided. The system comprises a client/server-analysis component for analyzing one or more queries that are not supported by a target database system, and a client/server transformation component for transforming the one or more queries into semantically equivalent queries that are supported by the target database system.

Claims

1-10. -10. (canceled)

11. A method of transforming an original database query into a supported database query that can be fully computed by a target database, the original database query comprising a select list including a plurality of expressions, the plurality of expressions each having a control break, the plurality of expressions including an expression that can not be directly computed by the target database, the method comprising:

constructing a derived table from the unsupported database query comprising;

constructing a new select list of the derived table;

traversing the plurality of expressions of the select list of the unsupported database query and for each of the expressions;

determining a corresponding expression to add to the new select list; and

adding the corresponding expression to the new select list; and

adding a GROUP BY expression to the derived table based on the new select list of the derived table; and

constructing the supported database query using the unsupported database query and the derived table.

22. The method as claimed in claim 11, wherein determining if an aggregate is eligible comprises:

a. determining that the aggregate is a report aggregate that has a standard aggregate counterpart.

23. The method as claimed in claim 11, wherein determining that all control breaks are compatible comprises:

a. determining that the longest control break is of the form (C₁, C₂, . . . , C_n) and all other control breaks are of the form( ) or of the form (C₁, C₂, . . . , C_k) where 0<

=k<

=n, where;

i. n is the number of columns in the longest control break; and

ii. k is the number of columns in the other control break.

24. A computer-readable medium having computer readable code embodied therein for use in the execution in a computer of a method of transforming an original database query into a supported database query that can be fully computed by a target database, the original database query comprising a select list including a plurality of expressions, the plurality of expressions each having a control break, the plurality of expressions including an expression that can not be directly computed by the target database, the computer comprising a processor coupled to the computer readable medium for executing the computer readable code, the method comprising:

a. constructing a derived table from the unsupported database query comprising;

i. constructing a new select list of the derived table;

ii. traversing the plurality of expressions of the select list of the unsupported database query and for each of the expressions;

1. determining a corresponding expression to add to the new select list; and

2. adding the corresponding expression to the new select list; and

iii. adding a GROUP BY expression to the derived table based on the new select list of the derived table; and

b. constructing the supported database query using the unsupported database query and the derived table.