GB2430770A - Query Generator - Google Patents

Abstract

A query generator for generating a query for retrieving data that satisfy a conditional expression from a database and/or performing an arbitrary calculation on at least part of the retrieved data is adapted to; receive an input query, analyse the input query to ascertain whether it contains a replaceable token; and if it does 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. The input and output queries may be structured query language (SQL) queries. A user can input a different parameter each time the expression is run allowing the generation of a diverse range of reports from a single input query.

Description

I

QUERY GENERATOR

The present invention relates to a method for generating queries and to a query generator, in particular for use with relational databases.

Query generators, for example Discoverer from Oracle , are used to construct database queries which are then sent to a database for execution. A user constructs a query by selecting items from a drop down list of items displayed on the screen. The items may represent data which are to be obtained from the database, or alternatively they may represent operations that are to be performed on these data. Once the items have been selected, the query generatorthen generates a query, usually in Structured Query Language (SQL), for execution by the database.

It is possible for a user to construct an SQL query that evaluates returned data items to see whether they meet a predefined condition. For example, a query may return data from a database table that represent the value of sales that have been made in each month of a year, and the SQL statement may incorporate a conditional expression that filters out all values for which the value of sales is below a certain amount. Other types of SQL statement may cause a calculation to be performed on the data. For example, a percentage value may be calculated for each of the returned sales values as a proportion of the total sales for all the months of the year. This percentage value would typically be presented to the user in the form of an extra column displayed in the table adjacent the returned value of the sales for each month.

However, this represents a rather inflexible approach since the query operates in exactly the same way each time it is executed, and it is therefore necessary to generate totally separate queries for all types of filtering and calculation operation which may be required. Obviously, this increases significantly the effort required to build and maintain the queries, and requires increased storage capacity for storing the queries.

In accordance with one aspect of the invention, there is provided a query generator for generating a query for retrieving data that satisfy a conditional expression from a database and/or performing an arbitrary calculation on at least part of the retrieved data, wherein the query generator is adapted to: a) receive an input query; b) analyse the input query to ascertain whether it contains a replaceable token; and c) if it does, 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.

In accordance with the second aspect of the invention, there is provided a method for generating a query for retrieving data that satisfy a conditional expression from a database and/or performing an arbitrary calculation on at least part of the retrieved data, wherein the method comprises: a) receiving an input query; b) analysing the input query to ascertain whether it contains a replaceable token; and C) if it does, replacing 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.

Hence, by allowing a replaceable token to be present in an input query, detecting the presence of such a replacement token when the query is invoked and allowing the user to replace that token with a parameter of a conditional expression and/or arbitrary calculation, the disadvantages of the prior art are overcome. In particular, the user can input a different parameter each time the expression is run allowing the generation of a diverse range of reports from a single input query.

Typically, the input and output queries are SQL queries.

Of course, the output query is normally executed on the database, thereby retrieving the data that satisfy the conditional expression and/or performing the arbitrary calculations.

The results of any arbitrary calculation are normally displayed in a column juxtaposed with a table containing the data on which the calculation was performed.

In a preferred embodiment, the user input is validated to ensure that it meets predefined criteria. This prevents the user from entering parameters which are clearly nonsensical and would cause an error on execution.

In some circumstances it may be that the parameter specified by the user input is an expression.

In accordance with a third aspect of the invention, a computer program comprises computer program code means adapted to perform the method of the second aspect of the invention when said program is run on a computer.

In accordance with a fourth aspect of the invention, a computer program product comprises program code means stored on a computer readable medium for performing the method of the second aspect of the invention when said program is run on a computer.

An embodiment of the invention will now be described with reference to the accompanying drawings, in which: Figure 1 shows a system on which the invention may be implemented; Figure 2 shows a database table showing sample data on which the invention may be performed; and Figure 3 shows a flowchart describing the method performed by the invention in a schematic form.

Figure 1 shows a server I which is connected to a database 2. The server I is operable to receive requests from client computers 3, 4, 5 via a network 6. This network 6 may be a local area network (LAN) or indeed it may be the Internet. Each of the client computers 3, 4, 5 runs query generator software that can construct a query in response to user input and transmit the query over network 6 to the server 1. The server I then executes the received query and extracts the necessary data from database 2 and performs any necessary computations on it before returning the results over the network 6 to the respective client computer 3, 4, or 5.

Figure 2 shows sample data on which the invention may be utilised. In particular, it shows a table known as Sales" which has three columns known as "MONTH", "SALES_AMOUNT" and "PROJECTED_SALES". These store data items whose values represent the months of the year, the amount of sales made in that month and the expected amount of sales for that month respectively. For example, it can be seen that the amount of sales in May was 15000 but the projected amount was 16000.

If a user required to retrieve all rows from this table where the corresponding value of SALES_AMOUNT was greater than 15000, they would construct a query using the query generator for which the following SQL statement would be generated: SELECT MONTH, SALES_AMOUNT

FROM SALES

WHERE SALES_AMOUNT> 15000 The WHERE clause in this SQL statement acts to filter out all data values for which the SALES_AMOUNT value is less than 15000. It does this by evaluating each retrieved value against the conditional expression of: SALES_AMOUNT> 15000. This type of expression is represented internally by the query generator in the form of an expression tree. As will be appreciated by those skilled in the art, an expression tree is made up of a plurality of nodes, which may include function nodes, item nodes and value nodes. In the above example, the function node is the greater than operator (>), the item node is the SALES_AMOUNT data item and the value node is 15000.

Another type of node that can make up an expression tree is a parameter node. For example, the value 15000 in the previous SQL statement may be replaced by the parameter PARAM and the parameter PARAM represents a parameter node in the expression tree. Previously, when the query generator has been caused to invoke a particular expression containing a parameter node such as this, a dialogue box was placed on the screen requesting that the user enter a literal value to replace the parameter. In this example, they would replace the parameter with the literal value 15000.

However, with this invention it is possible to replace the parameter node by an item node, and this gives the user the capability of entering an expression rather than a literal value to replace the parameter. Thus, for example the user may replace the parameter with the expression PROJECTED_SALES * 1.1 such that the query only retrieves those values for which the sales amount exceeds the projected sales amount by at least 10%.

Two examples will now be described with reference to Figures 2 and 3 to illustrate the invention. In the first example, an SQL query exists as shown below: SELECT MONTH, SALES_AMOUNT FROM SALES WHERE SALES_AMOUNT> PARAM This query contains a parameter PARAM. In step 10 of Figure 3, the query is received by the query generator software after it has been invoked by a user, and the query is analysed in step 11 to see whether it contains a parameter or token which should be replaced. During this analysis step, the expression tree is parsed node by node to detect the presence of each parameter node. If none is found then processing simply proceeds to step 14 which will be described later.

However, for each parameter node that is found (for example, the node PARAM in this case), a message is sent in step 12 to user interface software causing it to request (usually by way of a dialogue box) user input for the detected parameter node. Thus, in this case the user is prompted to enter an expression to replace the parameter node PARAM. Because, in this example, the user requires to see all values of sales which exceed the projected value by at least 10%, he will enter the expression PROJECTED_SALES * 1.1. In step 13, this user input is converted into an item node by the software and used to replace the parameter node PARAM.

There may be multiple parameter nodes in an expression tree, in which case user input is gathered for each of them. Once the user input for each parameter node has been collected, each parameter node is replaced by a new item node if the user entered an expression or a new value node if the user entered a literal value.

In step 14, the query is sent over network 6 to the server 1. The server 1 then executes the query in step 15 and retrieves the results from database 2. In step 16 these results are returned to the originating client computer 3, 4 or 5 which then displays the results to the user in step 17.

In this case, the sales for April, June and August exceed their projected sales targets by more than 10%, and so the following results table will be shown to the user:

MONTH SALES_AMOUNT

APRIL 18000 JUNE 17000 AUGUST 20000 In a second example, a filtering operation is not performed, but instead the software is required to perform a calculation on the retrieved data. In this case, the user requires to see each of the sales amounts by month, along with the value of each sales amount converted into another currency. The starting SQL statement for this is: SELECT MONTH, SALES_AMOUNT, PARAM FROM SALES In this case, the query generator software receives the input query when the query is invoked by a user in step 10. In step lithe query is analysed to determine whether it has a parameter or token to be replaced. In this case since it does processing proceeds to step 12 where the user is prompted to enter an item to replace the parameter. In this case, since they wish to see the US dollar equivalent of the SALES_AMOUNT value they will enter the following "SALES_AMOUNT *1.8!! (assuming that 1 is equal to US $1.8).

In step 13, the parameter PARAM is replaced by "SALES_AMOUNT * 1.8" to complete the query which is then sent to the server in step 14. Processing then proceeds as with the first example. However, the server returns to the client not only the values for the MONTH and SALES_AMOUNT columns but also a separate column representing SALES_AMOUNT x 1.8. The results are displayed to the user as shown in the following table: MONTH SALES_AMOUNT SALES_AMOUNT * 1.8 JANUARY 10000 18000 FEBRUARY 11000 19800 MARCH 9000 16200 APRIL 18000 32400 MAY 15000 27000 JUNE 17000 30600 JULY 12000 21600 AUGUST 20000 36000 SEPTEMBER 15000 27000 OCTOBER 16000 28800 NOVEMBER 18000 32400 DECEMBER 12000 21600 Thus, the invention provides very flexible reporting facilities. For example, in the first example, the base report can be used to show months where the SALES_AMOUNT values were higher or lower than expected by running two consecutive passes with different expressions substituted for the parameter PARAM.

Similarly, the second report can easily adopt to differing exchange rates, and may be used to produce tables with the SALES_AMOUNT values converted into many different currencies.

It is important to realise that the types of expression or calculation entered may be simple or arbitrarily complex. All that is required is that a valid expression or calculation is entered that can be resolved by the database. To this end, some validation of the input is performed in step 12 shown in Figure 3. For example, the validation process may check that the expression entered is valid in the sense that it refers to entities actually stored on the database (for example, the SALES_AMOUNT column referred to before) and/or that the expression does not contain a comparison or assignment of inconsistent types of data (for example, character data with numeric data).

It is important to note that while the present invention has been described in the context of a fully functioning data processing system, those of ordinary skill in the art will appreciate that the processes of the present invention are capable of being distributed in the form of a computer readable medium of instructions and a variety of forms and that the present invention applies equally regardless of the particular type of signal bearing media actually used to carry out the distribution. Examples of computer readable media include recordable-type media such as floppy disc, a hard disk drive, RAM, and CD-ROMs, as well as transmissiontype media, such as digital and analog communications links.

Claims (16)

1. A query generator for generating a query for retrieving data that satisfy a conditional expression from a database and/or performing an arbitrary calculation on at least part of the retrieved data, wherein the query generator is adapted to: a) receive an input query; b) analyse the input query to ascertain whether it contains a replaceable token; and C) if it does, 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.

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 either of the preceding claims, 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 any of the preceding claims, 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 any of the preceding claims, wherein the parameter specified by the user input is an expression.

7. A method for generating a query for retrieving data that satisfy a conditional expression from a database and/or performing an arbitrary calculation on at least part of the retrieved data, wherein the method comprises: a) receiving an input query; b) analysing the input query to ascertain whether it contains a replaceable token; and C) if it does, replacing 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.

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

9. A method according to either of claims 7 or 8, 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 any of claims 7 to 10, wherein the method further comprises validating the user input to ensure that it meets predefined criteria.

12. A method according to any of claims 7 to 11, wherein the parameter specified by the user input is an expression.

13. A computer program comprising computer program code means adapted to perform the method of any of claims 7 to 12 when said program is run on a computer.

14. A computer program product comprising program code means stored on a computer readable medium for performing the method of any of claims 7 to 12 when said program is run on a computer.

15. A query generator substantially as hereinbefore described with reference to the accompanying drawings.

16. A method substantially as hereinbefore described with reference to the accompanying drawings.