Patent ID: 7539660
Filing Date: 2009-05-26
Classification: G06F,Y10S

Abstract:
1. A computer implemented process for creating a database query comprising: receiving a template which contains a necessary query language for the database query and a plurality of placeholders, but lacks a plurality of clauses required for the database query to produce a desired output; receiving a user entry of a plurality of fields, a plurality of filters, and a sort criteria; responsive to receiving the user entry of the plurality of fields, the plurality of filters, and the sort criteria, retrieving a plurality of aliases for the plurality of fields and the plurality of filters from a column map of a database; generating a SELECT clause within the plurality of clauses based on the plurality of fields; generating an ORDER BY clause within the plurality of clauses based on the sort criteria; generating a first portion of a WHERE clause within the plurality of clauses based on the plurality of filters; creating a first aliases list, which is initially empty; creating a second aliases list, which is initially empty; determining whether each of the plurality of aliases is present in the first aliases list; responsive to a determination a first alias from the plurality of aliases is not present in the first aliases list, performing steps comprising: when the template contains a FROM placeholder among the plurality of placeholders, inserting the first alias with a matching table name into a FROM clause within the plurality of clauses; when the template does not contain the FROM placeholder among the plurality of placeholders but contains a JOIN placeholder among the plurality of placeholders, inserting the first alias with a matching table name and join criteria into a JOIN clause within the plurality of clauses; when the template contains neither the FROM placeholder among the plurality of placeholders nor the JOIN placeholder among the plurality of placeholders, returning an error; generating a second portion of the WHERE clause based on the JOIN clause for the first alias; adding the first alias to the first aliases list; adding the first alias and the second portion of the WHERE clause to the second aliases list, when the second portion of the WHERE clause depends on a second alias from the plurality of aliases, wherein the second aliases list contains a list of aliases, each of the aliases paired with a corresponding optional Where clause; determining whether each of the plurality of aliases in the second aliases list is present in the first aliases list; responsive to a determination that a third alias is present in both the second aliases list and the first aliases list, adding the optional Where clause corresponding to the third alias to the second portion of the WHERE clause; adding the first portion and the second portion of the WHERE clause; replacing the plurality of placeholders with the plurality of clauses; wherein the database query joins only a plurality of tables from the database necessary to complete the query only when those aliases found in the first aliases list and in the second aliases list were added to the second portion of the WHERE clause; and querying, using a computer, the database with only the set of necessary tables; obtaining an output from the database; and wherein unnecessary table joins are prevented.