Multi-part looked-up table field and its use to in data processing operations involving multiple tables of a relational database
View Patent ↗In accordance with a first aspect, a software component is equipped to identify looked-up table fields in a data processing statement, and automatically includes with a SQL statement one or more appropriate JOIN clauses joining one or more target tables from which the table fields are to be looked up with a basis 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, in response to the selection of the look-up table field. In one embodiment, the second aspect is practiced in conjunction with the automatic inclusion of appropriate JOIN clauses to a SQL statement of the first aspect.
1. A method comprising:
parsing a data processing statement;
identifying table field or fields referenced in said data processing statement;
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;
generating a SQL statement, including with said generated SQL statement field or fields to be selected from said basis table and a FROM clause enumerating said basis table, and if the data processing statement was determined to contain one or more fields to be looked up from one or more target tables, further including among said field or fields to be selected said one or more fields to be looked up from said one or more target tables, and one or more JOIN clauses respectively joining said basis table and said one or more target tables, and one or more corresponding ON clauses respectively specifying one or more corresponding conditions on which rows of said basis and said one or more target tables are to be joined, each of said one or more conditions comprising a corresponding look-up field.
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 look-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 one or more predetermined special characters comprises at least a selected one of “.”, “:”, “~”, “!”, “@”, “#”, “$”, “%”, “^”, “&”, “*”, “−”, “+”, “=”, “?”, “<” and “>”.
6. The method of claim 1 , wherein said JOIN clause is an OUTER JOIN clause.
7. The method of claim 1 , wherein said JOIN clause is an INNER JOIN clause.
8. The method of claim 1 , wherein said SQL statement is a selected one of a SELECT, an INSET, an UPDATE and a DELETE statment.
9. 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, 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 field or fields to be selected from said basis table and a FROM clause enumerating said basis table, and if the data processing statement was determined to contain one or more fields to be looked up from one or more target tables, further including among said field or fields to be selected said one or more fields to be looked up from said one or more target tables, and one or more JOIN clauses respectively joining said basis table and said one or more target tables, and one or more corresponding ON clauses respectively specifying one or more corresponding conditions on which rows of said basis and said one or more target tables are to be joined, each of said one or more conditions comprising a corresponding look-up field; and
one or more processors coupled to the storage medium to execute the programming instructions.
10. The apparatus of claim 9 , 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.
11. The apparatus of claim 10 , 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.
12. The apparatus of claim 11 , 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.
13. The apparatus of claim 12 , wherein said one or more predetermined special characters comprises at least a selected one of “.”, “:”, “~”, “!”, “@”, “#”, “$ ”, “%”, “^”, “&”, “−”, “+”, “=”, “?”, “<” and “>”.
14. The apparatus of claim 9 , wherein said JOIN clause is an OUTER JOIN clause.
15. The apparatus of claim 9 , wherein said JOIN clause is an INNER JOIN clause.
16. The apparatus of claim 9 , wherein said SQL statement is a selected one of a SELECT, an INSERT, an UPDATE and a DELETE statement.