Method for retrieving data from a database

Details Method for retrieving data from a database Method for retrieving data from a database

2001-04-20

2004-06-08

Alam, Shahid (Department: 2172)

Data processing: database and file management or data structures

Database design

Data structure types

C707S793000, C707S793000

Reexamination Certificate

active

06748378

ABSTRACT:

FIELD OF THE INVENTION
The present invention relates to relational databases and in particular to a method for retrieving data from such databases.
BACKGROUND TO THE INVENTION
The data stored in a relational database is commonly accessed and retrieved using a query and analysis tool. This acts as an interface, which may be graphical, between the user and the database with the purpose of hiding the complexity of the database query language, typically Structured Query Language (SQL), from the user.
For example, the database may contain a table named EMP which contains the names, salaries and departments of the employees of a company. The columns of this table may be entitled NAME, SAL and DEPT respectively. The user may require to rank the employees within each department by their annual salary. The corresponding SQL statement for this would be:
SELECT RANK ( ) OVER (PARTITION BY DEPT ORDER BY SAL) FROM EMP
However, using the query and analysis tool, the user could simply select an item known as “Rank by Annual Salary” which has been predefined to reference the above SQL statement. The query and analysis tool transfers the above SQL statement to the database for execution and presents the results generated to the user using an appropriate output device.
Such an SQL expression, where the data is first retrieved from the database and then processed in several passes, is known as an analytic or n-pass expression. Whilst this provides a powerful query tool, SQL does not currently allow one n-pass expression to reference another n-pass expression. As a result of this limitation, whilst it is possible to rank the employees in the above example by their total salary, it is not possible to rank them by their salary as a proportion of the total of all salaries.
SUMMARY OF THE INVENTION
In accordance with one aspect of the present invention, there is provided a method of generating a SQL statement for retrieving a data set from a database via a user interface and a database server and then processing the data set recursively, the method comprising the following steps:
a) inputting a nested n-pass expression to the user interface in a form that the database server cannot execute; and
b) converting the nested n-pass expression into a SQL statement that the database server can execute, the SQL statement having a level of recursion for each level of nesting of the n-pass expression, each level of recursion operating in use on the results of at least one of the preceding levels.
Hence, a nested n-pass expression may be entered by the user and converted into a form that is executable by the database server. Thus, the previously mentioned limitation has been overcome.
In a preferred example, the n-pass expression is processed by a parser and the output from the parser is processed by a SQL transformation routine.
The parser validates the syntax of the n-pass expression and then creates a hierarchical tree of operators and operands from the n-pass expression.
The SQL transformation routine traverses the hierarchical tree, splits the hierarchical tree into sub-trees, each sub-tree representing one level of recursion of the SQL statement, and references the sub-trees using aliases.
In accordance with a second aspect of the invention, there is provided a method of retrieving a data set from a database via a user interface and a database server and then processing the data set recursively, the method comprising generating a SQL statement in accordance with the first aspect of the invention and subsequently performing the following steps:
c) transferring the SQL statement from the user interface to the database server;
d) executing the SQL statement; and
e) transferring the result generated by the execution of the SQL statement from the database server to the user interface.
The user interface may be remote from the server and communicate with it over a medium such as the Internet, telephone, satellite, cable or other data link.
According to a third aspect of the invention, a computer program is provided for performing a method according to either the first or second aspects of the invention.
According to a fourth aspect of the invention, apparatus is provided for generating a SQL statement for retrieving a data set from a database and then processing it recursively, the apparatus comprising at least one store for storing the database, a database server, input means and a processor, the processor being adapted to execute a program for:
a) receiving a nested n-pass expression from the input means in a form that the database cannot execute; and
b) converting the nested n-pass expression into a SQL statement that the database server can execute.
In a typical example, the program parses the nested n-pass expression and then transforms the parsed nested n-pass expression into an SQL statement.
The syntax of the nested n-pass expression is validated during the parsing operation and then a hierarchical tree of operators and operands is created from the validated n-pass expression.
The program traverses the hierarchical tree, splits the hierarchical tree into sub-trees and uses aliases to reference the sub-trees.
According to a fifth aspect of the invention an apparatus is provided for retrieving a data set from a database and then processing it recursively according to the fourth aspect of the invention, wherein the program subsequently:
c) transfers the SQL statement to the database server for execution; and
d) receives the result generated by the execution of the SQL statement.


REFERENCES:
patent: 5724570 (1998-03-01), Zeller et al.
patent: 5852822 (1998-12-01), Srinivasan et al.
patent: 6006214 (1999-12-01), Carey et al.
Ganski et al., Optimization of Nested SQL Queries Revisited, ACM, 1987, 23-33.

Affiliated with

Also associated with

Rating

Method for retrieving data from a database does not yet have a rating. At this time, there are no reviews or comments for this patent.

If you have personal experience with Method for retrieving data from a database, we encourage you to share that experience with our LandOfFree.com community. Your opinion is very important and Method for retrieving data from a database will most certainly appreciate the feedback.

Rate now

Comments { 0 }

Profile ID: LFUS-PAI-O-3300663