System and method of processing queries
View Patent ↗A parallel detail join system for processing a many-to-one-to-many relationship is provided. The system comprises a parallel detail analysis module for detecting a many-to-one-to-many relationship among elements, and a parallel detail transformation module for generating a SQL: 1999 statement to handle the many-to-one-to-many relationship.
1. A computer implemented system for processing a many-to-one-to-many relationship, the system comprising:
a parallel detail analysis module for detecting a many-to-one-to-many relationship among elements; and
a parallel detail transformation module for generating a SQL: 1999 statement to handle the many-to-one-to-many relationship, the parallel detail transformation module including:
means for constructing a first LEFT OUTER JOIN between a master table and a first detail table based on a first foreign key relationship;
means for constructing a first select list containing a primary key of the master table, detail information from the master table and the first detail table, and the OLAP function specification ROW_NUMBER( ) OVER (PARTITION BY M.P 1 ORDER BY M.P 1 );
means for constructing a first temporary derived table based on the select list and the outer join;
means for constructing a second LEFT OUTER JOIN between the master table and a second detail table based on a second foreign key relationship;
means for constructing a second list containing the primary key, detail information from the master table and the second detail table, and the OLAP function specification ROW_NUMBER( ) OVER (PARTITION BY M.P 1 ORDER BY M.P 1 );
means for constructing a second temporary derived table based on the second select list and the second outer join; and
means for constructing a final SELECT statement that stitches together the first and second derived tables using a FULL OUTER JOIN with a join condition based on the primary key and the ROW_NUMBER functions.
2. A method of processing a many-to-one-to-many relationship, the method comprising the steps of:
detecting a many-to-one-to-many relationship among elements; and
generating a SQL: 1999 statement to handle the many-to-one-to-many relationship, including the steps of:
constructing a first LEFT OUTER JOIN between a master table and a first detail table based on a first foreign key relationship;
constructing a first select list containing a primary key of the master table, detail information from the master table and the first detail table, and the OLAP function specification ROW_NUMBER( ) OVER (PARTITION BY M.P 1 ORDER BY M.P 1 );
constructing a first temporary derived table based on the select list and the outer join;
constructing a second LEFT OUTER JOIN between the master table and a second detail table based on a second foreign key relationship;
constructing a second list containing the primary key, detail information from the master table and the second detail table, and the OLAP function specification ROW_NUMBER( ) OVER (PARTITION BY M.P 1 ORDER BY M.P 1 );
constructing a second temporary derived table based on the second select list and the second outer join; and
constructing a final SELECT statement that stitches together the first and second derived tables using a FULL OUTER JOIN with a join condition based on the primary key and the ROW_NUMBER functions.
3. A computer-readable medium having computer readable code embodied therein for use in the execution in a computer of a method of processing a many-to-one-to-many relationship, the method comprising the steps of:
detecting a many-to-one-to-many relationship among elements; and
generating a SQL: 1999 statement to handle the many-to-one-to-many relationship, including the steps of:
constructing a first LEFT OUTER JON between a master table and a first detail table based on a first foreign key relationship;
constructing a first select list containing a primary key of the master table, detail information from the master table and the first detail table, and the OLAP function specification ROW_NUMBER( ) OVER (PARTITION BY M.P 1 ORDER BY M.P 1 );
constructing a first temporary derived table based on the select list and the outer join;
constructing a second LEFT OUTER JON between the master table and a second detail table based on a second foreign key relationship;
constructing a second list containing the primary key, detail information from the master table and the second detail table, and the OLAP function specification ROW_NUMBER( ) OVER (PARTITION BY M.P 1 ORDER BY M.P 1 );
constructing a second temporary derived table based on the second select list and the second outer join; and
constructing a final SELECT statement that stitches together the first and second derived tables using a FULL OUTER JOIN with a join condition based on the primary key and the ROW_NUMBER functions.
4. A computer program product for use in the execution in a computer of a parallel detail join system for processing a many-to-one-to-many relationship, the computer program product comprising:
a parallel detail analysis module for detecting a many-to-one-to-many relationship among elements; and
a parallel detail transformation module for generating a SQL: 1999 statement to handle the many-to-one-to-many relationship, the parallel detail transformation module including:
means for constructing a first LEFT OUTER JOIN between a master table and a first detail table based on a first foreign key relationship;
means for constructing a first select list containing a primary key of the master table, detail information from the master table and the first detail table, and the OLAP function specification ROW_NUMBER( ) OVER (PARTITION BY M.P 1 ORDER BY M.P 1 );
means for constructing a first temporary derived table based on the select list and the outer join;
means for constructing a second LEFT OUTER JOIN between the master table and a second detail table based on a second foreign key relationship;
means for constructing a second list containing the primary key, detail information from the master table and the second detail table, and the OLAP function specification ROW_NUMBER( ) OVER (PARTITION BY M.P 1 ORDER BY M.P 1 );
means for constructing a second temporary derived table based on the second select list and the second outer join; and
means for constructing a final SELECT statement that stitches together the first and second derived tables using a FULL OUTER JOIN with a join condition based on the primary key and the ROW_NUMBER functions.