Query generation

Patent metadata

Field	Value
Publication number	US-9934330-B2
Application number	US-201313873231-A
Country	US
Kind code	B2
Filing date	Apr 30, 2013
Priority date	Nov 20, 2006
Publication date	Apr 3, 2018
Grant date	Apr 3, 2018

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.

First claim

What is claimed is:

1. A method for generating queries, the method comprising:
receiving, at a computer system, a query;
identifying, by the computer system, a first replaceable token, a second replaceable token, and a third replaceable token in the query, wherein:
the first replaceable token comprises a first alias that designates a first instance of a first parameter;
the second replaceable token comprises a second alias that designates a second instance of the first parameter;
the third replaceable token comprises the first alias that designates a reuse of the first instance of a first parameter in the query;

retrieving, by the computer system, a parameter definition for the parameter from a plurality of parameter definitions, wherein the parameter definition comprises a text prompt that requests an input providing a value for the parameter;
generating, by the computer system, a first graphical user interface (GUI) comprising the text prompt that requests an input providing a value for the parameter;
receiving, by the computer system, and through the first GUI, input representing a first value for the parameter;
replacing, by the computer system, the first replaceable token and the third replaceable token in the query with the first value for the parameter;
generating, by the computer system, a second GUI comprising the text prompt that requests an input providing a value for the parameter;
receiving, by the computer system, and through the first GUI, input representing a second value of the parameter;
replacing, by the computer system, the second replaceable token in the query with the second value for the parameter; and
submitting, by the computer system, the query for evaluation.

2. The method of claim 1 , further comprising:
determining that the first value for the parameter comprises a conditional expression;
determining that the second value for the parameter comprises a numerical value;
evaluating the conditional expression before submitting the query for evaluation;
receiving query results in response to submitting the query for evaluation; and
multiplying the query results by the numerical value before displaying the results.

3. The method of claim 2 , further comprising displaying the results of multiplying the query results by the numerical value in a column juxtaposed with a column containing the data on which the multiplication was performed.

4. The method of claim 1 , further comprising:
determining whether receiving a value for the parameter is optional and not mandatory based on the parameter definition; and
determining that the first value for the parameter is a null value, wherein:
replacing the first replaceable token in the query with the first value for the parameter comprises, based on determining whether receiving a value for the parameter is optional, nullifying a conditional expression in the query that includes the first replaceable token.

5. The method of claim 4 , wherein nullifying the conditional expression comprises replacing the conditional expression with an expression that always evaluates to TRUE such that any other conditional expressions in the query are still be evaluated.

6. The method of claim 1 , wherein the query comprises a structured query language (SQL) query.

7. The method of claim 1 , further comprising validating the input to ensure that it meets predefined criteria from the parameter definition.

8. The method of claim 1 , wherein the parameter definition further comprises:
a parameter name;
a default value for the parameter;
a field designating whether multiple values are allowed;
a field designating whether the parameter is optional or mandatory; and
an item or field that is related to the parameter.

9. A system comprising:
one or more processors;
one or more memory devices comprising instructions that, when executed by the one or more processors, cause the one or more processors to perform operations comprising:
receiving a query;
identifying a first replaceable token, a second replaceable token, and a third replaceable token in the query, wherein:
the first replaceable token comprises a first alias that designates a first instance of a first parameter;
the second replaceable token comprises a second alias that designates a second instance of the first parameter;
the third replaceable token comprises the first alias that designates a reuse of the first instance of a first parameter in the query;

retrieving a parameter definition for the parameter from a plurality of parameter definitions, wherein the parameter definition comprises a text prompt that requests an input providing a value for the parameter;
generating a first graphical user interface (GUI) comprising the text prompt that requests an input providing a value for the parameter;
receiving and through the first GUI, input representing a first value for the parameter;
replacing the first replaceable token and the third replaceable token in the query with the first value for the parameter;
generating a second GUI comprising the text prompt that requests an input providing a value for the parameter;
receiving and through the first GUI, input representing a second value of the parameter;
replacing the second replaceable token in the query with the second value for the parameter; and
submitting the query for evaluation.

10. The system of claim 9 , wherein the one or more memories further comprise additional instructions that, when executed by the one or more processors, cause the one or more processors to perform operations comprising:
determining that the first value for the parameter comprises a conditional expression;
determining that the second value for the parameter comprises a numerical value;
evaluating the conditional expression before submitting the query for evaluation;
receiving query results in response to submitting the query for evaluation; and
multiplying the query results by the numerical value before displaying the results.

11. The system of claim 10 , wherein the one or more memories further comprise additional instructions that, when executed by the one or more processors, cause the one or more processors to perform operations comprising:
displaying the results of multiplying the query results by the numerical value in a column juxtaposed with a column containing the data on which the multiplication was performed.

12. The system of claim 9 , wherein the one or more memories further comprise additional instructions that, when executed by the one or more processors, cause the one or more processors to perform operations comprising:
determining whether receiving a value for the parameter is optional and not mandatory based on the parameter definition; and
determining that the first value for the parameter is a null value, wherein:
replacing the first replaceable token in the query with the first value for the parameter comprises, based on determining whether receiving a value for the parameter is optional, nullifying a conditional expression in the query that includes the first replaceable token.

13. The system of claim 12 , wherein nullifying the conditional expression comprises replacing the conditional expression with an expression that always evaluates to TRUE such that any other conditional expressions in the query are still be evaluated.

14. The system of claim 9 , wherein the query comprises a structured query language (SQL) query.

15. The system of claim 9 , wherein the one or more memories further comprise additional instructions that, when executed by the one or more processors, cause the one or more pro

Assignees

• Oracle Int Corp

Inventors

• Kearsey Natascha
• Evans Christian
• Hogg Martin
• Fragapane Paolo
• Corbett Steve

Classifications

• G06F17/30401

  Physics · mapped topic

• G06F17/30967 Primary

  Physics · mapped topic

• G06F16/243 Primary

  Natural language query formulation · CPC title

• G06F16/9032 Primary

  Query formulation · CPC title