Query generator

• US 7,467,152 B2
• Filed: 12/07/2000
• Issued: 12/16/2008
• Est. Priority Date: 03/16/2000
• Status: Expired due to Term

• Current Assignee: Oracle International Corporation (Oracle Corporation)
• Original Assignee: Oracle International Corporation (Oracle Corporation)
• Inventors: Kearsey, Natascha, Osborn, Andrew, Fragapane, Paolo, Cave, Stephen David, Lavender, Robert Lloyd, Nash, Kathryn
• Primary Examiner(s): AL HASHEMI, SANA A
• Application Number:
  US09/730,826
• Publication Number:
  US 20020010695A1
• Time in Patent Office: 2,931 Days
• Field of Search: 707/2, 707/3, 707/4, 707/5, 707/6, 707/9
• US Class Current: 1/1
• CPC Class Codes: G06F 16/2428 Query predicate definition using graphical user interfaces, including menus and forms G06F16/2423 takes precedence
• CPC Class Codes: Y10S 707/99932 Access augmentation or optimizing
• CPC Class Codes: Y10S 707/99942 Manipulating data structure, e.g. compression, compaction, compilation
• CPC Class Codes: Y10S 707/99943 Generating database or data structure, e.g. via user interface

Abstract

The present invention relates to a query generator for generating a query for obtaining selected data from a database. The query generator includes a processor which is adapted to receive an input indicating the selected data to be obtained. The processor then analyses the input to determine whether the input requires a joining of data in different tables, and an aggregation step. If so, the processor generates a query which causes the database to aggregate the data within each of the tables as required; and, join the aggregated data, the joined aggregated data representing the selected data.

Claims

1. A query generator for generating a query for obtaining selected data from a database, the database having a number of detail tables in which data is stored, the query generator comprising a processor which is coupled to the database in use, the processor being adapted to:

a. receive an input indicating the selected data to be obtained to generate a first query;

b. analyze the input and determine whether the input requires a joining of data in a plurality of different detail tables, and an aggregation step; and

,

c. if it is determined the input requires a joining of data in a plurality of different detail tables, causing the processor to modify the content of the first query indicating the selected data to be obtained to generate a second query, the second query being adapted to cause the database to;

i. aggregate the data within each of the plurality of detail tables as required; and

,

ii. perform a SQL join operation to join the aggregated data from each of the plurality of detail tables, the joined aggregated data representing the selected data.

2. A query generator according to claim 1, wherein the second query causes the database to aggregate the data for each table by causing the database to:

i. select the data within the table for aggregation; and

,

ii.aggregate the data.

3. A query generator according to claim 2, wherein the data is selected by implementing complex folders to generate inline views within the database, the inline views being maintained until the aggregation is completed.

4. A query generator according to claim 1, wherein the aggregation comprises at least one of:

generating an average of the data, generating a sum of the data, generating a standard deviation of the data, and generating an evaluation of either a maximum or a minimum of the data.

5. A query generator according to claim 1, the query generator further comprises an input device coupled to the processor to allow a user to generate the input.

6. A query generator according to claim 5, wherein the input is generated by selecting items from a list of possible items, each item representing data contained within a respective database table, an action to be performed on data within the database, or both data contained within a respective database table and an action to be performed on data within the database.

7. A query generator according to claim 1, wherein the first query and the second query being generated as SQL queries.

8. A query generator according to claim 1, wherein the aggregation step requires the aggregation of data in different tables.

9. A database system, the database system comprising:

a. a database, the database comprising;

i. a store for storing data, the store having a number of detail tables; and

,

ii. a database processor coupled to the store for obtaining data in accordance with a received query; and

,

b. a query generator for generating a structured query for obtaining selected data from the database, the query generator comprising a processor adapted to;

i. receive an input representing a first structured query to be generated;

ii.analyze the input and determine whether the input requires a joining of data in a plurality of different detail tables, and an aggregation step; and

,

iii. if the input requires a joining of data in a plurality of different detail tables, causing the processor to modify the content of the first query indicating the selected data to be obtained to generate a second structured query,

wherein the database processor responds to the second structured query to;

(1) aggregate the data within each of the plurality of detail tables as required; and

,

(2) perform a SQL join operation to join the aggregated data from each of the plurality of detail tables, the joined aggregated data representing the selected data.

10. A system according to claim 9, wherein the database processor and the processor of the query generator are a same processor.

11. A system according to claim 9, wherein the database processor aggregates the data for each table by:

i. selecting the data within the table for aggregation; and

,

ii.aggregating the data.

12. A system according to claim 11, wherein the database processor selects the data by implementing an inline view to generate complex folders within the database, the complex folders being maintained until the aggregation is completed.

13. A system according to claim 9, wherein the aggregation comprises at least one of:

generating an average of the data, generating a sum of the data, generating a standard deviation of the data, and generating a maximum and a minimum evaluation of the data.

14. A system according to claim 9, the query generator further comprising an input device coupled to the processor of the query generator to allow a user to generate the input.

15. A system according to claim 14, wherein the input is generated by selecting items from a list of possible items, each item representing data contained within a respective database table, an action to be performed on data within the database, or both data contained within a respective database table and an action to be performed on data within the database.

16. A query generator for generating a query for obtaining selected data from a database, the database having a number of detail tables in which data is stored, the query generator comprising a processor which is coupled to the database in use, the processor being adapted to:

a. receive an input indicating the selected data to be obtained to generate a first query;

b. analyze the input and determine whether the input requires a joining of data in a plurality of different detail tables, and an aggregation step; and

,

c. if it is determined the input requires a joining of data in a plurality of different detail tables, causing the processor to modify the content of the first query indicating the selected data to be obtained to generate a second query, the second query being adapted to cause the database to;

i. aggregate the data within each of the plurality of detail tables as required; and

,

ii. perform a SQL join operation to join the aggregated data from each of the plurality of detail tables, the joined aggregated data representing the selected data;

wherein the data is selected by implementing complex folders to generate inline views within the database, the inline views being maintained until the aggregation is completed.

17. A computer program product for generating a query for obtaining selected data from a database, the database having a number of detail tables in which data is stored, comprising:

a computer readable medium;

computer program instructions, recorded on the computer readable medium, executable by a processor, for performing the steps of;

a. receiving an input indicating the selected data to be obtained to generate a first query;

b. analyzing the input and determine whether the input requires a joining of data in a plurality of different detail tables, and an aggregation step; and

,

c. if it is determined the input requires a joining of data in a plurality of different detail tables, causing the processor to modify the content of the first query indicating the selected data to be obtained to generate a second query, the second query being adapted to cause the database to;

i. aggregate the data within each of the plurality of detail tables as required; and

,

ii. perform a SQL join operation to join the aggregated data from each of the plurality of detail tables, the joined aggregated data representing the selected data.

18. The computer program product of claim 17, wherein the second query causes the database to aggregate the data for each table by causing the database to:

i. select the data within the table for aggregation; and

,

ii. aggregate the data.

19. The computer program product of claim 18, wherein the data is selected by implementing complex folders to generate inline views within the database, the inline views being maintained until the aggregation is completed.

20. The computer program product of claim 17, wherein the aggregation comprises at least one of:

generating an average of the data, generating a sum of the data, generating a standard deviation of the data, and generating an evaluation of either a maximum or a minimum of the data.