Query generation

• US 8,447,771 B2
• Filed: 11/20/2006
• Issued: 05/21/2013
• Est. Priority Date: 11/20/2006
• Status: Active Grant

• Current Assignee: Oracle International Corporation (Oracle Corporation)
• Original Assignee: Oracle International Corporation (Oracle Corporation)
• Inventors: Kearsey, Natascha, Evans, Christian, Hogg, Martin, Fragapane, Paolo, Corbett, Steve
• Primary Examiner(s): LEWIS, ALICIA M
• Application Number:
  US11/602,537
• Publication Number:
  US 20080120326A1
• Time in Patent Office: 2,374 Days
• Field of Search: None
• US Class Current: 707/759
• 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 method for generating queries for at least one of retrieving data that satisfy a conditional expression from a database and performing a calculation on at least part of the retrieved data, the method comprising:

receiving, at one or more computer systems, a first query containing at least one first conditional expression;

determining, with one or more processors associated with the one or more computer systems, a first replaceable token and a second replaceable token in the first query;

retrieving, with the one or more processors associated with the one or more computer systems, a parameter definition for a first instance of a parameter corresponding to the first replaceable token in the first query from a pool of parameter definitions that exist outside of a report or query that includes the first query;

verifying that the at least one first conditional expression in the query references the parameter having the persistent parameter definition;

in response to the at least one first conditional expression referencing the parameter having the persistent parameter definition, obtaining user input for a first instance of the parameter and user input for a second instance of the parameter based on the persistent parameter definition, wherein obtaining user input comprises;

generating, with the one or more processors associated with the one or more computer systems, a first graphical user interface in response to processing the first query based on the parameter definition for the first instance of the parameter corresponding to the first replaceable token identified in the first query, the parameter definition including information configured to prompt users for information indicative of a conditional expression and a calculation, wherein the first graphical user interface is sent and displayed to a user as a prompt for first information indicative of a second conditional expression and a first calculation;

receiving first user input from a user, wherein the first user input comprises the second conditional expression and first calculation;

generating, with the one or more processors associated with the one or more computer systems, a second graphical user interface in response to processing the first query based on reusing the parameter definition for the second instance of the parameter corresponding to the second replaceable token identified in the first query, wherein the second graphical user interface is sent and displayed to a user as a prompt for second information indicative of a third conditional expression and a second calculation;

receiving second user input from a user, wherein the second user input comprises the third conditional expression and second calculation;

replacing the first replaceable token with the second conditional expression and the first calculation and replacing the second token with the third conditional expression and the second calculation;

in response to replacing the first replaceable token identified in the first query with the second conditional expression and the first calculation and the second replaceable token identified in the first query with the third conditional expression and the second calculation, performing generation of a second query for retrieving data that satisfy the at least one first conditional expression;

retrieving data that satisfies the at least one first conditional expression, wherein retrieving data that satisfies the at least one first conditional expression comprises evaluating the second conditional expression and the third conditional expression; and

performing the first calculation and the second calculation on the retrieved data.

2. The method of claim 1 wherein generating, with the one or more processors associated with the one or more computer systems, the second query further comprises generating the second query to include one or more mandatory parameters defined in the parameter definition.

3. The method of claim 1 wherein generating, with the one or more processors associated with the one or more computer systems, the second query further comprises generating the second query such that the results of performing the calculation are juxtaposed with the data on which the calculation was performed.

4. The method of claim 1 wherein at least one of the first query and the second query are structured query language (SQL) queries.

5. The method of claim 1 further comprising:

executing, with the one or more processors associated with the one or more computer systems, the second query against the database.

6. The method of claim 1 further comprising:

validating, with the one or more processors associated with the one or more computer systems, that the second conditional expression and the first calculation input by the user or the third conditional expression and the second calculation input by the user meets predefined criteria.

7. The method of claim 1 wherein the parameter definition includes at least one of the following properties:

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

8. A non-transitory computer-readable medium storing computer-executable code for generating queries for at least one of retrieving data that satisfy a conditional expression from a database and performing a calculation on at least part of the retrieved data, the non-transitory computer-readable medium comprising:

code for receiving a first query containing at least one first conditional expression;

code for determining a first replaceable token and a second replaceable token in the first query;

code for retrieving a parameter definition for a first instance of a parameter corresponding to the first replaceable token in the first query from a pool of parameter definitions that exist outside of a report or query that includes the first query;

code for verifying that the at least one first conditional expression in the query references the parameter having the persistent parameter definition;

in response to the at least one first conditional expression referencing the parameter having the persistent parameter definition, code for obtaining user input for a first instance of the parameter and user input for a second instance of the parameter based on the persistent parameter definition, wherein obtaining user input comprises;

code for generating a first graphical user interface in response to processing the first query based on the parameter definition for the first instance of the parameter corresponding to the first replaceable token identified in the first query, the parameter definition including information configured to prompt users for information indicative of a conditional expression and a calculation, wherein the first graphical user interface is sent and displayed to a user as a prompt for first information indicative of a second conditional expression and a first calculation;

code for receiving first user input from a user, wherein the first user input comprises the second conditional expression and first calculation;

code for generating a second graphical user interface in response to processing the first query based on reusing the parameter definition for the second instance of the parameter corresponding to the second replaceable token identified in the first query, wherein the second graphical user interface is sent and displayed to a user as a prompt for second information indicative of a third conditional expression and a second calculation;

code for receiving second user input from a user, wherein the second user input comprises the third conditional expression and second calculation;

code for replacing the first replaceable token with the second conditional expression and the first calculation and replacing the second token with the third conditional expression and the second calculation;

in response to replacing the first replaceable token identified in the first query with the second conditional expression and the first calculation and the second replaceable token identified in the first query with the third conditional expression and the second calculation, code for performing generation of a second query for retrieving data that satisfy the at least one first conditional expression;

code for retrieving data that satisfies the at least one first conditional expression, wherein retrieving data that satisfies the at least one first conditional expression comprises evaluating the second conditional expression and the third conditional expression; and

code for performing the first calculation and the second calculation on the retrieved data.

9. The non-transitory computer-readable medium of claim 8 wherein the code for generating the second query further comprises code for generating the second query to include one or more mandatory parameters defined in the parameter definition.

10. The non-transitory computer-readable medium of claim 8 wherein the code for generating the second query further comprises code for generating the second query such that the results of performing the calculation are juxtaposed with the data on which the calculation was performed.

11. The non-transitory computer-readable medium of claim 8 wherein at least one of the first query and the second query are structured query language (SQL) queries.

12. The non-transitory computer-readable medium of claim 8 further comprising:

code for executing the second query against the database.

13. The non-transitory computer-readable medium of claim 8 further comprising:

code for validating that the second conditional expression and the first calculation input by the user or the third conditional expression and the second calculation input by the user meets predefined criteria.

14. The non-transitory computer-readable medium of claim 8 wherein the parameter definition includes at least one of the following properties:

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

15. A system for generating queries for at least one of retrieving data that satisfy a conditional expression from a database and performing a calculation on at least part of the retrieved data, the system comprising:

a processor; and

a memory in communication with the processor and configured to store a set of instructions which when executed by the processor configure the processor to;

receive a first query containing at least one first conditional expression;

determine a first replaceable token and a second replaceable token in the first query;

retrieve a parameter definition for a first instance of a parameter corresponding to the first replaceable token in the first query from a pool of parameter definitions that exist outside of a report or query that includes the first query;

verify that the at least one first conditional expression in the query references the parameter having the persistent parameter definition;

in response to the at least one first conditional expression referencing the parameter having the persistent parameter definition, obtain user input for a first instance of the parameter and user input for a second instance of the parameter based on the persistent parameter definition, wherein obtaining user input comprises;

generating a first graphical user interface in response to processing the first query based on the parameter definition for the first instance of the parameter corresponding to the first replaceable token identified in the first query, the parameter definition including information configured to prompt users for information indicative of a conditional expression and a calculation, wherein the first graphical user interface is sent and displayed to a user as a prompt for first information indicative of a second conditional expression and a first calculation;

receiving first user input from a user, wherein the first user input comprises the second conditional expression and first calculation;

generating a second graphical user interface in response to processing the first query based on reusing the parameter definition for the second instance of the parameter corresponding to the second replaceable token identified in the first query, wherein the second graphical user interface is sent and displayed to a user as a prompt for second information indicative of a third conditional expression and a second calculation;

receiving second user input from a user, wherein the second user input comprises the third conditional expression and second calculation;

replace the first replaceable token with the second conditional expression and the first calculation and replacing the second token with the third conditional expression and the second calculation;

in response to replacing the first replaceable token identified in the first query with the second conditional expression and the first calculation and the second replaceable token identified in the first query with the third conditional expression and the second calculation, perform generation of a second query for retrieving data that satisfy the at least one first conditional expression;

retrieve data that satisfies the at least one first conditional expression, wherein retrieving data that satisfies the at least one first conditional expression comprises evaluating the second conditional expression and the third conditional expression; and

perform the first calculation and the second calculation on the retrieved data.

16. The system of claim 15 wherein the processor is further configured to generate the second query to include one or more mandatory parameters defined in the parameter definition.

17. The system of claim 15 wherein the processor is further configured to generate the second query such that the results of the calculation are juxtaposed with the data on which the calculation was performed.

18. The system of claim 15 wherein at least one of the first query and the second query are structured query language (SQL) queries.

19. The system of claim 15 wherein the processor is further configured to execute the second query against the database.

20. The system of claim 15 wherein the processor is further configured to validate that the second conditional expression and the first calculation input by the user or the third conditional expression and the second calculation input by the user meets predefined criteria.

21. The system of claim 15 wherein the parameter definition includes at least one of the following properties:

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