Multi-part looked-up table fields and its use in data processing operations involving multiple tables of a relational database

• US 20030131215A1
• Filed: 01/10/2002
• Published: 07/10/2003
• Est. Priority Date: 10/25/2001
• Status: Active Grant

• Current Assignee: Oracle International Corporation (Oracle Corporation)
• Original Assignee: BEA Systems Incorporated (Oracle Corporation)
• Inventors: Bellew, Matthew A.
• Granted Patent: US 7,076,482 B2
• Time in Patent Office: Days
• US Class Current: 712/200
• CPC Class Codes: G06F 16/2423 Interactive query statement specification based on a database schema
• CPC Class Codes: Y10S 707/99932 Access augmentation or optimizing
• 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
• CPC Class Codes: Y10S 707/99943 Generating database or data structure, e.g. via user interface

Abstract

In accordance with a first aspect, a software component is equipped to identify non-looked-up table fields and looked-up table fields to be grouped, and table fields having aggregate functions to be performed in their row values in a data processing statement, and automatic inclusion with a SQL statement a subquery to generate a grouped derivative table comprising grouped non-looked-up table fields and aggregated table fields, and one or more appropriate JOIN clauses joining one or more target tables from which the table fields are to be looked up with the grouped derivative table. In one embodiment, the looked-up table fields are expressed in a multi-part form. In accordance with a second aspect, a software component is equipped to automatically expand table fields available for inclusion in a data processing operation to include table fields of a target table of a look-up table field, and selection of aggregation function.

Claims

1. A method comprising:

parsing a data processing statement;

identifying table field or fields referenced in said data processing statement, including whether an aggregation operation is to be performed on row values of each of the identified table fields;

for each identified table field, determining whether the table field is a looked-up field;

identifying a basis table of which non-looked up ones of said identified table field or fields are members;

identifying one or more target tables from which said looked-up one or ones of said identified table field or fields are to be looked up; and

generating a SQL statement, including with said generated SQL statement a FROM clause having a subquery creating a grouped derivative table comprising grouped non-looked-up table fields and aggregated table fields, and one or more JOIN clauses joining the corresponding one or more target tables to the grouped derivative table, if the data processing statement is determined to contain first one or more table fields to have aggregation operations performed on their row values.

2. The method of claim 1, wherein said determining of whether a table field is a looked-up field comprises determining whether the table field is a multi-part table field including at least a first part corresponding to a look-up field, and a second part corresponding to a field to be looked up, concatenated with said first part in a predetermined manner.

3. The method of claim 2, wherein said determining of whether a table field is a looked-up field further comprises upon determining that the table field is a multi-part table field, determining whether the second part is a look-up field, with a third part corresponding to a looked up field concatenated with said second part in a predetermined manner.

4. The method of claim 2, wherein said second part corresponding to a field to be looked up, is concatenated with said first part corresponding to a look-up field, employing one or more predetermined special characters.

5. The method of claim 4, wherein said conditional generating of a SQL statement, when performed, comprises generating said subquery in a form of a SELECT statement enumerating identified table fields of said basis table, including the aggegration functions to be performed on applicable ones of the identified table fields, including with said SELECT statement a first FROM clause enumerating said basis table, and a GROUP BY clause enumerating again said enumerated table field or fields of the basis table that have not been identified as having aggregation functions to be performed.

6. The method of claim 5, wherein said conditional generating of a SQL statement, when performed, further comprises enumerating field or fields to be selected from said grouped derivative table and said one or more target tables, a second FROM clause enumerating said subquery, an AS clause enumerating an identifier of the grouped derivative table, and said one or more JOIN clauses respectively joining said grouped derivative table and said one or more target tables.

7. The method of claim 1, wherein said aggregation function is a selected one of a counting function (COUNT), a minimum value identification function (MIN), a maximum value identification function (MAX), an average value computing function (AVG) and a value summation function (SUM).

8. The method of claim 1, wherein said SQL statement is a selected one of a SELECT, an INSERT, an UPDATE and a DELETE statement.

9. A method comprising:

presenting a first plurality of fields of a first table for selection for use in a data processing operation;

receiving a selection of a first field that is a member of said first fields;

determining whether said selected first field is a first designated look-up field for looking up first one or more of a second plurality of fields of a second table;

presenting said second plurality of fields for selection for use in said data processing operation, if it is determined that that said selected first field is a first designated look-up field for looking up first one or more of said second plurality of fields of said second table;

receiving a request to perform an aggregation function on a selected one of said first and second plurality of fields; and

presenting a plurality of aggregation functions for selection.

10. The method of claim 9, wherein each of said second plurality of fields is presented in a multi-part form, including a first part, said first look-up field, and a second part, a corresponding one of the second one or more fields to be looked up, concatenated with said first part in a predetermined manner.

11. The method of claim 9, wherein said method further comprises

receiving a selection of a second field that is a member of said second fields;

determining whether said selected second field is a second designated look-up field for looking up second one or more of a third plurality of fields of a third table; and

presenting said third plurality of fields for selection if it is determined that said selected second field is a second designated look-up field for looking up second one or more of said third plurality of fields of said third table.

12. The method of claim 11, wherein

each of said second plurality of fields is presented in a multi-part form, including a first part, said first look-up field, and a second part, a corresponding one of said first one or more fields to be looked up, concatenated with said first part in a predetermined manner;

and

each of said third plurality of fields is presented in a multi-part form, including said first and second parts, and a third part, a corresponding one of said second one or more fields to be looked up, concatenated with said second part in a predetermined manner.

13. The method of claim 10, wherein said second part, a corresponding one of said first one or more fields to be looked up, is concatenated with said first part, said first look-up field, employing one or more predetermined special characters.

14. The method of claim 9, wherein the method further comprises generating a SQL statement, including with said generated SQL statement a FROM clause having a subquery creating a grouped derivative table comprising grouped non-looked-up fields and aggregated fields, and one or more JOIN clauses joining corresponding one or more target tables of one or more looked-up fields to the grouped derivative table, if one or more selected fields are to have aggregation operations performed on their row values.

15. The method of claim 14, wherein said conditional generating of a SQL statement, when performed, comprises generating said subquery in a form of a SELECT statement enumerating selected fields of said basis table, including the aggegration functions to be performed on applicable ones of the selected fields, including with said SELECT statement a first FROM clause enumerating said basis table, and a GROUP BY clause enumerating again said enumerated selected field or fields of the basis table that have not have aggregation functions selected for performance on their row values.

16. The method of claim 15, wherein said conditional generating of a SQL statement, when performed, further comprises enumerating selected field or fields to be selected from said grouped derivative table and said one or more target tables, a second FROM clause enumerating said subquery, an AS clause enumerating an identifier of the grouped derivative table, and said one or more JOIN clauses respectively joining said grouped derivative table and said one or more target tables.

17. The method of claim 15, wherein said aggregation function is a selected one of a counting function (COUNT), a minimum value identification function (MIN), a maximum value identification function (MAX), an average value computing function (AVG) and a value summation function (SUM).

18. The method of claim 15, wherein said SQL statement is a selected one of a SELECT, an INSERT, an UPDATE and a DELETE statement.

19. The method of claim 9, wherein the method further comprises

specifying said first plurality of fields of said first table;

and

designating one or more of said specified first fields as look-up fields; and

specifying target tables for said designated look-up fields.

20. An apparatus comprising:

storage medium having stored therein programming instructions, when executed, operate the apparatus to

parse a data processing statement,

identify table field or fields referenced in said data processing statement, including whether an aggregation operation is to be performed on row values of each of the identified table fields,

determine, for each identified table field, whether the table field is a looked-up field,

identify a basis table of which non-looked up ones of said identified table field or fields are members,

identify one or more target tables from which said looked-up one or ones of said identified table field or fields are to be looked up, and

generate a SQL statement, including with said generated SQL statement a FROM clause having a subquery creating a grouped derivative table comprising grouped table fields and aggregated table fields, and one or more JOIN clauses joining the corresponding one or more target tables to the grouped derivative table, if the data processing statement is determined to contain first one or more table fields to have aggregation operations performed on their row values; and

one or more processors coupled to the storage medium to execute the programming instructions.

21. The apparatus of claim 20, wherein said programming instructions, when executed, enable the apparatus to determine whether a table field is a looked-up field by determining whether the table field is a multi-part table field including at least a first part corresponding to a look-up field, and a second part corresponding to a field to be looked up, concatenated with said first part in a predetermined manner.

22. The apparatus of claim 21, wherein said programming instructions, when executed, enable the apparatus to, upon determining that the table field is a multi-part table field, determine whether the second part is also a look-up field, with a third part corresponding to a looked up field concatenated with said second part in a predetermined manner.

23. The apparatus of claim 22, wherein said second part corresponding to a field to be looked up, is concatenated with said first part corresponding to a look-up field, employing one or more predetermined special characters.

24. The apparatus of claim 20, wherein said programming instructions, when executed, enable the apparatus to perform said conditional generating of a SQL statement by generating said subquery in a form of a SELECT statement enumerating identified table fields of said basis table, including the aggegration functions to be performed on applicable ones of the identified table fields, including with said SELECT statement a first FROM clause enumerating said basis table, and a GROUP BY clause enumerating again said enumerated table field or fields of the basis table that have not been identified as having aggregation functions to be performed.

25. The apparatus of claim 24, wherein said programming instructions, when executed, further enable the apparatus to enumerate field or fields to be selected from said grouped derivative table and said one or more target tables, a second FROM clause enumerating said subquery, an AS clause enumerating an identifier of the grouped derivative table, and said one or more JOIN clauses respectively joining said grouped derivative table and said one or more target tables, to conditionally generate said SQL statement.

26. The apparatus of claim 20, wherein said aggregation function is a selected one of a counting function (COUNT), a minimum value identification function (MIN), a maximum value identification function (MAX), an average value computing function (AVG) and a value summation function (SUM).

27. The apparatus of claim 20, wherein said SQL statement is a selected one of a SELECT, an INSERT, an UPDATE and a DELETE statement.

28. An apparatus comprising:

storage medium having stored therein a plurality of programming instructions, when executed, operate the apparatus to

present a first plurality of fields of a first table for selection for use in a data processing operation,

receive a selection of a first field that is a member of said first fields,

determine whether said selected first field is a first designated look-up field for looking up first one or more of a second plurality of fields of a second table,

present said second plurality of fields for selection for use in said data processing operation, if it is determined that that said selected first field is a first designated look-up field for looking up first one or more of said second plurality of fields of said second table,

receive a request to perform an aggregation function on a selected one of said first and second plurality of fields, and

present a plurality of aggregation functions for selection; and

at least one processor coupled to the storage medium to execute the programming instructions.

29. The apparatus of claim 28, wherein said programming instructions, when executed, operate the apparatus to present each of said second plurality of fields in a multi-part form, including a first part, said first look-up field, and a second part, a corresponding one of said first one or more fields to be looked up, concatenated with said first part in a predetermined manner.

30. The apparatus of claim 29, wherein said programming instructions, when executed, further operate the apparatus to

receive a selection of a second field that is a member of said second fields;

determine whether said selected second field is a second designated look-up field for looking up second one or more of a third plurality of fields of a third table; and

present said third plurality of fields for selection if it is determined that said selected second field is a second designated look-up field for looking up second one or more of said third plurality of fields of said third table.

31. The apparatus of claim 30, wherein said programming instructions, when executed, operate the apparatus to present

each of said second plurality of fields is presented in a multi-part form, including a first part, said first look-up field, and a second part, a corresponding one of said first one or more fields to be looked up, concatenated with said first part in a predetermined manner;

and

each of said third plurality of fields is presented in a multi-part form, including said first and second parts, and a third part, a corresponding one of said second one or more fields to be looked up, concatenated with said second part in a predetermined manner.

32. The apparatus of claim 29, wherein said second part, a corresponding one of said first one or more fields to be looked up, is concatenated with said first part, said look-up field, employing one or more predetermined special characters.

33. The apparatus of claim 28, wherein said programming instructions, when executed, further enable the apparatus to generating a SQL statement, including with said generated SQL statement a FROM clause having a subquery creating a grouped derivative table comprising grouped non-looked-up fields and aggregated fields, and one or more JOIN clauses joining corresponding one or more target tables of one or more looked-up fields to the grouped derivative table, if one or more selected fields are to have aggregation operations performed on their row values

34. The apparatus of claim 33, wherein the programming instructions, when executed, enable the apparatus to perform said conditional generation of a SQL statement by generating said subquery in a form of a SELECT statement enumerating selected fields of said basis table, including the aggegration functions to be performed on applicable ones of the selected fields, including with said SELECT statement a first FROM clause enumerating said basis table, and a GROUP BY clause enumerating again said enumerated selected field or fields of the basis table that have not have aggregation functions selected for performance on their row values.

35. The apparatus of claim 34, wherein the programming instructions, when executed, enable the apparatus to enumerate selected field or fields to be selected from said grouped derivative table and said one or more target tables, a second FROM clause enumerating said subquery, an AS clause enumerating an identifier of the grouped derivative table, and said one or more JOIN clauses respectively joining said grouped derivative table and said one or more target tables.

36. The apparatus of claim 28, wherein said aggregation function is a selected one of a counting function (COUNT), a minimum value identification function (MIN), a maximum value identification function (MAX), an average value computing function (AVG) and a value summation function (SUM).

37. The apparatus of claim 28, wherein said SQL statement is a selected one of a SELECT, an INSERT, an UPDATE and a DELETE statement.

38. The apparatus of claim 28, wherein the programming instructions, when executed, further operate the apparatus to

specify said first plurality of fields of said first table,

designate one or more of said specified first fields as look-up fields, and

specify target tables for said designated look-up fields.