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

• US 7,451,138 B2
• Filed: 10/18/2005
• Issued: 11/11/2008
• Est. Priority Date: 10/25/2001
• Status: Expired due to Term

• Current Assignee: Oracle International Corporation (Oracle Corporation)
• Original Assignee: BEA Systems Incorporated (Oracle Corporation)
• Inventors: Bellew, Matthew A.
• Primary Examiner(s): Lee; Wilson
• Assistant Examiner(s): SHECHTMAN, CHERYL MARIA
• Application Number:
  US11/252,939
• Publication Number:
  US 20060053089A1
• Time in Patent Office: 1,120 Days
• Field of Search: 707/4, 707/102, 707/201, 707/2
• US Class Current: 1/1
• 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/99934 Query formulation, input preparation, or translation
• CPC Class Codes: Y10S 707/99943 Generating database or data structure, e.g. via user interface
• CPC Class Codes: Y10S 707/99952 Coherency, e.g. same view to multiple users

Abstract

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.

Claims

1. 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, in response to the selection of said first field, 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 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; and

generating a SQL statement, including with said generated SQL statement field or fields to be selected from said first table and a FROM clause enumerating said first table, and if one or more of said fields to be looked up from said second table are also selected, further including among said field or fields to be selected said one or more fields to be looked up from said second table, and a JOIN clause joining said second table to said first table, and an ON clause specifying a condition on which rows of said second and said first tables are to be joined, said condition comprising said look-up field.

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

3. The method of claim 2, 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.

4. The method of claim 3, wherein said one or more predetermined special characters comprises at least a selected one of “

.”

, “

;

”

, “

˜

”

, “

!”

, “

@”

, “

#”

,“

$”

, “

%”

, “

^”

, “

&

”

, “

*”

, “

−

”

, “

+”

, “

=”

, “

?”

, “

<

” and

“

>

”

.

5. The method of claim 1, wherein said method further comprises

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

determining, in response to the selection of said second field, whether said selected second field is a second designated look-up field for looking up a 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 a second one or more of said third plurality of fields of said third table.

6. The method of claim 5, 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.

7. The method of claim 1, wherein said JOIN clause is an OUTER JOIN clause.

8. The method of claim 1, wherein said JOIN clause is an INNER JOIN clause.

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

10. The method of claim 1, 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.

11. 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, in response to the selection of said first field, 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 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,and

generating a SQL statement, including with said generated SQL statement field or fields to be selected from said first table and a FROM clause enumerating said first table, and if one or more of said fields to be looked up from said second table are also selected, further including among said field or fields to be selected said one or more fields to be looked up from said second table, and a JOIN clause joining said second table to said first table, and an ON clause specifying a condition on which rows of said second and said first tables are to be joined, said condition comprising said look-up field; and

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

12. The apparatus of claim 11, 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 corresponding to said first look-up field, and a second part a corresponding to one of said first one or more fields to be looked up, where said second part is concatenated with said first part in a predetermined manner.

13. The apparatus of claim 12, 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, in response to the selection of said second field, whether said selected second field is a second designated look-up field for looking up a 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 a second one or more of said third plurality of fields of said third table.

14. The apparatus of claim 13, 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.

15. The apparatus of claim 12, 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.

16. The apparatus of claim 15, wherein said one or more predetermined special characters comprises at least a selected one of “

.”

, “

;

”

, “

˜

”

, “

!”

, “

@”

, “

#”

, “

$”

, “

%”

, “

^”

, “

&

”

, “

*”

, “

−

”

, “

+”

, “

=”

, “

?”

, “

<

” and

“

>

”

.

17. The apparatus of claim 11, wherein said JOIN clause is an OUTER JOIN clause.

18. The apparatus of claim 11, wherein said JOIN clause is an INNER JOIN clause.

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

20. The apparatus of claim 11, 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.