Query conditions having filtered fields within a data abstraction environment

• US 9,031,924 B2
• Filed: 09/04/2008
• Issued: 05/12/2015
• Est. Priority Date: 02/26/2002
• Status: Active Grant

• Current Assignee: Workday Incorporated
• Original Assignee: International Business Machines Corporation
• Inventors: Dettinger, Richard D., Glowacki, Janice R., Kulack, Frederick A.
• Primary Examiner(s): Pham, Khanh
• Assistant Examiner(s): HARPER, ELIYAH STONE
• Application Number:
  US12/204,250
• Publication Number:
  US 20080319969A1
• Time in Patent Office: 2,441 Days
• Field of Search: None
• US Class Current: 707/705
• CPC Class Codes: G06F 16/2423 Interactive query statement specification based on a database schema
• CPC Class Codes: G06F 16/2428 Query predicate definition using graphical user interfaces, including menus and forms G06F16/2423 takes precedence

Abstract

Embodiments of the invention provide techniques for composing and executing queries using filtered fields. In one embodiment, a query may be composed with compatible fields, meaning filtered fields that store related data of a particular data type. Such compatible fields may be combined into a single query condition, thus simplifying the composition and execution of the query. In one embodiment, fields may be identified as compatible based on metadata included in a database abstraction model.

Claims

1. A computer-implemented method, comprising:

providing an abstraction model defining a plurality of logical fields each having a respective access method specifying a technique for accessing underlying physical data, the access method selected from two or more distinct types of access methods, wherein the plurality of logical fields includes a plurality of filtered fields defined as having a filtered access method in the abstraction model, wherein the abstraction model includes, for each of the plurality of filtered fields, a filtered field definition specifying a respective;

(i) field name that uniquely identifies the filtered field and is distinct from a corresponding field name in the underlying physical data, (ii) first column, (iii) second column and (iv) filter value, wherein each filtered field is configured to return a subset of values from the first column based on which values within the second column correspond to the filter value;

receiving an abstract query including an abstract query condition that specifies two or more filtered fields of the plurality of filtered fields;

determining that the two or more filtered fields are compatible fields, based on a determination that the filtered field definitions for the two or more filtered fields all reference a same first column and a same second column of the underlying physical data; and

upon determining that the abstract query contains compatible fields;

generating an executable query from the abstract query by mapping fields from the abstract query to fields corresponding to values within the underlying physical data based on the abstraction model, wherein the abstract query condition specifying the two or more filtered fields is reflected in a single sub-query of the executable query; and

executing the executable query to produce a set of query results.

2. The method of claim 1, wherein the abstraction model contains a plurality of logical fields, inclusive of the plurality of filtered fields, wherein a respective logical field definition for each of the plurality of logical fields in the abstraction model specifies a respective access method specifying manner of accessing one or more physical fields in the underlying physical data, the respective access method selected from two or more different types of access methods, and wherein at least one of the two or more different types of access methods is a filtered access method.

3. The method of claim 2, wherein the single sub-query is composed using the IN operator of the SQL query language.

4. The method of claim 1, further comprising presenting the set of query results to a user in a graphical user interface.

5. The method of claim 1, wherein values corresponding to the two or more filtered fields are returned in one column of the set of query results.

6. The method of claim 1, wherein determining that the two or more filtered fields are compatible fields further comprises retrieving metadata describing the plurality of logical fields.

7. The method of claim 1, wherein determining that the two or more filtered fields are compatible fields further comprises retrieving, from the abstraction model, logical field specifications specifying the plurality of logical fields.

8. A computer-readable storage medium including a program, which when executed on a processor performs an operation, the operation comprising:

providing an abstraction model defining a plurality of logical fields each having a respective access method specifying a technique for accessing underlying physical data, the access method selected from two or more distinct types of access methods, wherein the plurality of logical fields includes a plurality of filtered fields defined as having a filtered access method in the abstraction model, wherein the abstraction model includes, for each of the plurality of filtered fields, a filtered field definition specifying a respective;

(i) field name that uniquely identifies the filtered field and is distinct from a corresponding field name in the underlying physical data, (ii) first column, (iii) second column and (iv) filter value, wherein each filtered field is configured to return a subset of values from the first column based on which values within the second column correspond to the filter value;

receiving an abstract query including an abstract query condition that specifies two or more filtered fields of the plurality of filtered fields;

determining that the two or more filtered fields are compatible fields, based on a determination that the filtered field definitions for the two or more filtered fields all reference a same first column and a same second column of the underlying physical data; and

upon determining that the abstract query contains compatible fields;

generating an executable query from the abstract query by mapping fields from the abstract query to fields corresponding to values within the underlying physical data based on the abstraction model, wherein the abstract query condition specifying the two or more filtered fields is reflected in a single sub-query of the executable query; and

executing the executable query to produce a set of query results.

9. The computer-readable storage medium of claim 8, wherein the executable query is composed using the SQL query language.

10. The computer-readable storage medium of claim 9, wherein the executable sub-query is composed using the IN operator of the SQL query language.

11. The computer-readable storage medium of claim 8, further comprising presenting the set of query results to a user in a graphical user interface.

12. The computer-readable storage medium of claim 8, wherein values corresponding to the two or more filtered fields are returned in one column of the set of query results.

13. The computer-readable storage medium of claim 8, wherein determining that the two or more filtered fields are compatible fields further comprises retrieving metadata describing the plurality of logical fields.

14. The computer-readable storage medium of claim 8, wherein determining that the two or more filtered fields are compatible fields further comprises retrieving, from the abstraction model, logical field specifications specifying the plurality of logical fields.

15. A system, comprising:

a processor; and

a memory containing a program, which when executed by the processor is configured to perform an operation, the operation comprising;

providing an abstraction model defining a plurality of logical fields each having a respective access method specifying a technique for accessing underlying physical data, the access method selected from two or more distinct types of access methods, wherein the plurality of logical fields includes a plurality of filtered fields defined as having a filtered access method in the abstraction model, wherein the abstraction model includes, for each of the plurality of filtered fields, a filtered field definition specifying a respective;

(i) field name that uniquely identifies the filtered field and is distinct from a corresponding field name in the underlying physical data, (ii) first column, (iii) second column and (iv) filter value, wherein each filtered field is configured to return a subset of values from the first column based on which values within the second column correspond to the filter value;

receiving an abstract query including an abstract query condition that specifies two or more filtered fields of the plurality of filtered fields;

determining that the two or more filtered fields are compatible fields, based on a determination that the filtered field definitions for the two or more filtered fields all reference a same first column and a same second column of the underlying physical data; and

upon determining that the abstract query contains compatible fields;

generating an executable query from the abstract query by mapping fields from the abstract query to fields corresponding to values within the underlying physical data based on the abstraction model, wherein the abstract query condition specifying the two or more filtered fields is reflected in a single sub-query of the executable query; and

executing the executable query to produce a set of query results.

16. The system of claim 15, wherein the executable query is composed using the SQL query language.

17. The system of claim 16, wherein the executable sub-query is composed using the IN operator of the SQL query language.

18. The system of claim 15, further comprising presenting the set of query results to a user in a graphical user interface.

19. The system of claim 15, wherein values corresponding to the two or more filtered fields are returned in one column of the set of query results.

20. The system of claim 15, wherein determining that the two or more filtered fields are compatible fields further comprises retrieving, from the abstraction model, logical field specifications specifying the plurality of logical fields.