METHOD, SYSTEM, PROGRAM AND DATA STRUCTURE FOR CLEANING A DATABASE TABLE

• US 20060195488A1
• Filed: 05/11/2006
• Published: 08/31/2006
• Est. Priority Date: 09/21/1999
• Status: Abandoned Application

• Current Assignee: International Business Machines Corporation
• Original Assignee: International Business Machines Corporation
• Inventors: Mandel, Richard Henry III, Cesare, Mark Anthony, Jerves, Julie Ann, Christopher, Tom Robert
• Application Number:
  US11/382,889
• Publication Number:
  US 20060195488A1
• Time in Patent Office: Days
• US Class Current: 1/1
• CPC Class Codes: G06F 16/215 Improving data quality; Data cleansing, e.g. de-duplication, removing invalid entries or correcting typographical errors
• CPC Class Codes: Y10S 707/99934 Query formulation, input preparation, or translation
• CPC Class Codes: Y10S 707/99936 Pattern matching access
• CPC Class Codes: Y10S 707/99942 Manipulating data structure, e.g. compression, compaction, compilation
• CPC Class Codes: Y10S 707/99943 Generating database or data structure, e.g. via user interface
• CPC Class Codes: Y10S 707/99945 Object-oriented database structure processing

Abstract

Disclosed is a method, system, program, and data structure for performing a clean operation on an input table. The input table to clean is indicated in an input data table name. At least one rule definition is processed to clean the input table. Each rule definition indicates a find criteria, a replacement value, and an input data column in the input table. For each rule definition, the input data column is searched for any fields that match the find criteria. The replacement value for the particular rule definition is inserted in the fields in the input data column that match the find criteria. Subsequent applications of additional rule definitions applied to the same input data column operate on replacement values inserted in the input data column during previously applied rule definitions. At least one of said rule definition includes multiple find criteria and a corresponding replacement value for each find criteria.

Claims

1. A computer implemented method for performing a clean operation on an input table having an input table name, comprising:

receiving at least one rule definition, wherein each rule definition indicates a find criteria, a replacement value, and an input data column in the input table;

searching, for each rule definition, the input data column for any fields that match the find criteria;

determining whether the rule definition specifies an output table; and

in response to determining that the rule definition does not specify an output table, directly inserting, for each rule definition, the replacement value in the fields in the input data column that match the find criteria, wherein subsequent applications of additional rule definitions applied to the same input data column operate on replacement values inserted in the input data column in previously applied rule definitions,

wherein at least one of said rule definition includes multiple find criteria and a corresponding replacement value for each find criteria, wherein searching the input data column comprises applying each of the multiple find criteria to one field until one of;

(i) a match occurs and (ii) none of the multiple find criteria are found to match the field content, wherein inserting the replacement value comprises inserting the replacement value corresponding to one find criteria that matched the field content, wherein a sort column includes values to use to sort the multiple find criteria and corresponding replacement value, and wherein searching comprises applying the multiple find criteria to each field in the order specified in the sort column.

2. The method of claim 1, wherein the find criteria for at least one rule definition comprises an upper bound and lower bound, and wherein searching comprises searching for any fields that have values within the upper and lower bounds.

3. The method of claim 2, wherein searching comprises searching for any fields that have values outside of one of the upper and lower bounds.

4. The method of claim 1, wherein each rule definition includes a row clean flag, and wherein at least one rule definition has the row clean flag set, further comprising removing any row including a field matching the search criteria from the input table when the row clean flag is set.

5. A computer implemented system for performing a clean operation on an input table having an input data table name, comprising;

means for receiving at least one rule definition, wherein each rule definition indicates a find criteria, a replacement value, and an input data column in the input table;

means for searching, for each rule definition, the input data column for any fields that match the find criteria;

means for determining whether the rule definition specifies an output table; and

means for, in response to determining that the rule definition does not specify an output table, directly inserting, for each rule definition, the replacement value in the fields in the input data column that match the find criteria, wherein subsequent applications of additional rule definitions applied to the same input data column operate on replacement values inserted in the input data column in previously applied rule definitions;

wherein at least one of said rule definition includes multiple find criteria and a corresponding replacement value for each find criteria, wherein the means for searching the input data column comprises applying each of the multiple find criteria to one field until one of;

(i) a match occurs and (ii) none of the multiple find criteria are found to match the field content, and wherein the means for inserting the replacement value comprises inserting the replacement value corresponding to one find criteria that matched the field content, wherein a sort column includes values to use to sort the multiple find criteria and corresponding replacement value, and wherein the means for searching comprises applying the multiple find criteria to each field in the order specified in the sort column.

6. The system of claim 5, wherein the find criteria for at least one rule definition comprises an upper bound and lower bound, and wherein searching comprises searching for any fields that have values within the upper and lower bounds.

7. The system of claim 6, wherein the means for searching comprises searching for any fields that have values outside of one of the upper and lower bounds.

8. The system of claim 5, wherein each rule definition includes a row clean flag, and wherein at least one rule definition has the row clean flag set, further comprising means for removing any row including a field matching the search criteria from the input table when the row clean flag is set.

9. An article of manufacture for use performing a clean operation on an input table in a database having an input data table name, the article of manufacture comprising computer usable media including at least one computer program embedded therein that causes the computer to perform:

receiving at least one rule definition, wherein each rule definition indicates a find criteria, a replacement value, and an input data column in the input table;

searching, for each rule definition, the input data column for any fields that match the find criteria;

determining whether the rule definition specifies an output table; and

in response to determining that the rule definition does not specify an output table, directly inserting, for each rule definition, the replacement value in the fields in the input data column that match the find criteria, wherein subsequent applications of additional rule definitions applied to the same input data column operate on replacement values inserted in the input data column in previously applied rule definitions;

wherein at least one of said rule definition includes multiple find criteria and a corresponding replacement value for each find criteria, wherein searching the input data column comprises applying each of the multiple find criteria to one field until one of;

(i) a match occurs and (ii) none of the multiple find criteria are found to match the field content, and wherein inserting the replacement value comprises inserting the replacement value corresponding to one find criteria that matched the field content, wherein a sort column includes values to use to sort the multiple find criteria and corresponding replacement value, and wherein searching comprises applying the multiple find criteria to each field in the order specified in the sort column.

10. The article of manufacture of claim 9, wherein the find criteria for at least one rule definition comprises an upper bound and lower bound, and wherein searching comprises searching for any fields that have values within the upper and lower bounds.

11. The article of manufacture of claim 10, wherein searching comprises searching for any fields that have values outside of one of the upper and lower bounds.

12. The article of manufacture of claim 9, wherein each rule definition includes a row clean flag, and wherein at least one rule definition has the row clean flag set, further comprising removing any row including a field matching the search criteria from the input table when the row clean flag is set.

13. A memory device including a command data structure, comprising:

an input data table name parameter indicating the input table subject to the clean operation; and

at least one rule definition, wherein each rule definition includes a find criteria, a replacement value, and an input data column in the input table, wherein, for each rule definition, the input data column is searched for any fields that match the find criteria, wherein, for each rule definition, it is determined whether the rule definition specifies an output table, and in response to determining that the rule definition does not specify an output table, the replacement value is directly inserted in the fields in the input data column that match the find criteria, and wherein subsequent applications of additional rule definitions applied to the same input data column operate on replacement values inserted in the input data column in previously applied rule definitions, wherein at least one of said rule definition further includes multiple find criteria and a corresponding replacement value for each find criteria, wherein the input data column is searched by applying each of the multiple find criteria to one field until one of;

(i) a match occurs and (ii) none of the multiple find criteria are found to match the field content, and wherein the replacement value corresponding to the matching find criteria is inserted into the field, wherein a sort column includes values to use to sort the multiple find criteria and corresponding replacement value, and wherein the multiple find criteria are applied to each field in the input data column in the order specified in the sort column.

14. The memory device of claim 13, wherein the find criteria for at least one rule definition comprises an upper bound and lower bound, and wherein the input data column is searched for any fields that have values within the upper and lower bounds.

15. The memory device of claim 14, wherein the input data column is searched for any fields that have values outside of one of the upper and lower bounds.

16. The memory device of claim 13, wherein each rule definition includes a row clean flag, and wherein at least one rule definition has the row clean flag set, and wherein any row including a field matching the search criteria is removed from the input table when the row clean flag is set.