Multi-part looked-up table fields and its use in data processing operations involving multiple tables of a relational database
First Claim
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.
3 Assignments
0 Petitions
Accused Products
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.
-
Citations
38 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. - View Dependent Claims (2, 3, 4, 5, 6, 7, 8)
-
-
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. - View Dependent Claims (10, 11, 12, 13, 14, 15, 16, 17, 18, 19)
-
-
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. - View Dependent Claims (21, 22, 23, 24, 25, 26, 27)
-
-
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. - View Dependent Claims (29, 30, 31, 32, 33, 34, 35, 36, 37, 38)
-
Specification