Query generation

• US 20080120326A1
• Filed: 11/20/2006
• Published: 05/22/2008
• Est. Priority Date: 11/20/2006
• Status: Active Grant

• Current Assignee: Oracle International Corporation (Oracle Corporation)
• Original Assignee: Oracle International Corporation (Oracle Corporation)
• Inventors: Corbett, Steve, Evans, Christian, Kearsey, Natascha, Hogg, Martin, Fragapane, Paolo
• Granted Patent: US 8,447,771 B2
• Time in Patent Office: Days
• US Class Current: 707/102
• CPC Class Codes: G06F 16/243 Natural language query formulation
• CPC Class Codes: G06F 16/9032 Query formulation

Abstract

A query generator is operable to generate a query for retrieving data that satisfy a conditional expression from a database, and/or perform an arbitrary calculation on at least part of the retrieved data. The query generator can receive an input query and analyze the query to ascertain whether it contains a replaceable token. If the query contains a replaceable token, the query generator can replace that token with user input specifying a parameter of the conditional expression and/or the arbitrary calculation, thereby generating an output query for retrieving the data that satisfy the conditional expression and/or performing the arbitrary calculation on the retrieved data.

Claims

1. A query generator operable to generate a query for at least one of retrieving data that satisfy a conditional expression from a database and performing an arbitrary calculation on at least part of the retrieved data, the query generator being adapted to:

a) receive an input query;

b) analyze the input query to ascertain whether the input query contains a replaceable token; and

c) if the input query contains a replaceable token, replace the replaceable token with at least one of user input specifying a parameter of the conditional expression and the arbitrary calculation, thereby performing at least one of generating an output query for retrieving the data that satisfy the conditional expression and performing the arbitrary calculation on the retrieved data.

2. A query generator according to claim 1, wherein the input and output queries are structured query language (SQL) queries.

3. A query generator according to claim 1, wherein the query generator is further adapted to execute the output query on the database, thereby retrieving the data that satisfy the conditional expression and/or performing the arbitrary calculation.

4. A query generator according to claim 3, wherein the query generator is further adapted to display the results of any arbitrary calculation performed in a column juxtaposed with a table containing the data on which the calculation was performed.

5. A query generator according to claim 1, wherein the query generator is further adapted to validate the user input to ensure that it meets predefined criteria.

6. A query generator according to claim 1, wherein the parameter specified by the user input is an expression.

17. An information management system according to claim 1, wherein the query generator is adapted to use the parameter input by the user, together with the persistent parameter definition to generate a structured query language (SQL) query.

7. A method for generating a query for at least one of retrieving data that satisfy a conditional expression from a database and performing an arbitrary calculation on at least part of the retrieved data, the method comprising:

a) receiving an input query;

b) analyzing the input query to ascertain whether the input query contains a replaceable token; and

c) if the input query contains a replaceable token, replacing the replaceable with at least one of user input specifying a parameter of the conditional expression and the arbitrary calculation, thereby generating an output query for at least one of retrieving the data that satisfy the conditional expression and performing the arbitrary calculation on the retrieved data.

8. A method according to claim 7, wherein the input and output queries are structured query language (SQL) queries.

9. A method according to claim 7, wherein the method further comprises executing the output query on the database, thereby retrieving the data that satisfy the conditional expression and/or performing the arbitrary calculation.

10. A method according to claim 9, wherein the method further comprises displaying the results of any arbitrary calculation performed in a column juxtaposed with a table containing the data on which the calculation was performed.

11. A method according to claim 7, wherein the method further comprises validating the user input to ensure that it meets predefined criteria.

12. A method according to claim 7, wherein the parameter specified by the user input is an expression.

24. A computer program comprising computer program code means adapted to perform the method of claim 7.

25. A computer program product comprising program code means stored on a computer readable medium for performing the method of claim 7 when said program is run on a computer.

13. An information management system comprising:

a first data store containing database data;

a query generator adapted to retrieve and output a subset of the database data that satisfies at least one conditional expression;

a user interface configured to receive input from, and output information to, a user; and

a second data store containing one or more persistent parameter definitions, each persistent parameter definition comprising data describing a parameter to be used in the at least one conditional expression;

wherein the query generator is further adapted to verify whether the at least one conditional expression references a parameter having a persistent parameter definition, and if it does, obtain user input via the user interface for the parameter based on the persistent parameter definition.

14. An information management system according to claim 13, wherein the persistent parameter definition comprises data describing at least one of the following properties:

the parameter name, a default value for the parameter, whether multiple values are allowed, whether the parameter is optional or mandatory, a text prompt used to prompt the user for input, the item or field the parameter is related to.

15. An information management system according to claim 13, wherein the persistent parameter definition is defined independently of the query generator.

16. An information management system according to claim 13, wherein the parameter input by the user is an expression.

18. A method for generating a query comprising:

a) defining one or more persistent parameter definitions, each persistent parameter definition comprising data describing a parameter to be used in a conditional expression;

b) selecting a data source;

c) receiving a query with at least one conditional expression, the at least one conditional expression defining a subset of data from the data source;

d) verifying whether the at least one conditional expression references a parameter having a persistent parameter definition;

e) if it does, obtaining user input for the parameter based on the persistent parameter definition and using said user input in the conditional expression; and

f) executing the query and formatting and returning said subset of data.

19. A method for generating a query according to claim 18, wherein the persistent parameter definition comprises data describing at least one of the following properties:

the parameter name, a default value for the parameter, whether multiple values are allowed, whether the parameter is optional or mandatory, a text prompt used to prompt the user for input, the item or field to which the parameter is related.

20. A method for generating a query according to claim 18, wherein step a) further comprises defining the persistent parameter definition independently of the query generator.

21. A method for generating a query according to claim 18, wherein the parameter input by the user is an expression.

22. A method for generating a query according to claim 18, wherein step e) further comprises using the parameter input by the user, together with the persistent parameter definition to generate a structured query language (SQL) query.

23. A method for generating a query according to claim 18, wherein the persistent parameter definition is used to validate and/or verify the user input of step e).

26. A computer program comprising computer program code means adapted to perform the method of claim 18 when said program is run on a computer.

27. A computer program product comprising program code means stored on a computer readable medium for performing the method of claim 18 when said program is run on a computer.