Computer-implemented data replacement graphical user interface system and method

• US 7,634,460 B2
• Filed: 06/28/2002
• Issued: 12/15/2009
• Est. Priority Date: 06/28/2002
• Status: Active Grant

• Current Assignee: SAS Institute Incorporated
• Original Assignee: SAS Institute Incorporated
• Inventors: Thorne, Gregory M., Pirrello, Kelly D.
• Primary Examiner(s): Wong; Don
• Assistant Examiner(s): DANG, THANH HA T
• Application Number:
  US10/186,224
• Publication Number:
  US 20040002941A1
• Time in Patent Office: 2,727 Days
• Field of Search: 707/102, 707/3, 707/104.1
• US Class Current: 1/1
• CPC Class Codes: G06F 16/235 Update request formulation
• CPC Class Codes: G06F 16/2428 Query predicate definition using graphical user interfaces, including menus and forms G06F16/2423 takes precedence
• CPC Class Codes: G06F 16/248 Presentation of query results
• CPC Class Codes: Y10S 707/99933 Query processing, i.e. searching
• 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

A computer-implemented graphical user interface (GUI) system and method are provided for modifying data via an SQL CASE statement. The GUI system and method displays at least one data value from results of a query, and allows a new data value to be specified. An SQL CASE statement is generated based upon the specified new data value, and a data value within the query results is modified based upon the generated SQL CASE statement.

Claims

1. A computer-implemented graphical user interface for modifying, through a Structured Query Language (SQL) CASE statement, data contained within query results, comprising:

instructions stored in a computer-readable storage medium and executable on one or more processors to generate;

a first interface region that displays at least one data value from the query results, the query results being created by an SQL SELECT statement database retrieval operation containing column parameters and filter parameters, the query results including current column data values resulting from execution of the SQL SELECT statement;

a second interface region that allows a user to specify a replacement column data value for a current column data value displayed in the query results, wherein the replacement column data value is a substitute for the current column data value displayed in the query results; and

an SQL CASE statement construction module having a data connection to the second interface region, the SQL CASE statement construction module generating an SQL CASE statement based upon the replacement column data value specified in the second interface region,

wherein the current column data value displayed in the query results is modified based upon execution of the generated SQL CASE statement.

2. The computer-implemented graphical user interface of claim 1, wherein the query results contain data derived from a relational database system.

3. The computer-implemented graphical user interface of claim 1, wherein a view is created from the query results, wherein the view is a virtual table derived from at least one table from a relational database system.

4. The computer-implemented graphical user interface of claim 1, wherein the data values contained within the query results are based upon data values correspondingly contained within a database, and wherein the generated SQL CASE statement modifies the current column data value in the query results without modifying the corresponding data value in the database.

5. The computer-implemented graphical user interface of claim 1, wherein a view is created from the query results, wherein the data values contained within the view are based upon data values correspondingly contained within a database, and wherein the generated SQL CASE statement modifies the data value in the view without modifying the corresponding data value in the database.

6. The computer-implemented graphical user interface of claim 5, wherein the data values contained within the view are created based upon an SQL database retrieval operation that operates upon data values contained within the database.

7. The computer-implemented graphical user interface of claim 5, wherein the data values contained within the view are created based upon an SQL CREATE VIEW operation that operates upon data values contained within the database.

8. The computer-implemented graphical user interface of claim 5, wherein the data values contained within the view are created based upon an SQL SELECT operation that operates upon data values contained within the database.

9. The computer-implemented graphical user interface of claim 1, wherein the user is allowed to select within the first interface region current values associated with a column in the query results.

10. The computer-implemented graphical user interface of claim 1, wherein the user is allowed to select within the first interface region current data values associated with a column in the query results, thereby relieving the user from needing to know valid column data values for assigning replacement column data values.

11. The computer-implemented graphical user interface of claim 1, wherein the first interface region displays the current column data value selected by the user for replacement within the query results.

12. The computer-implemented graphical user interface of claim 1, wherein the current column data value displayed in the query results is part of a data range selected by the user for replacement within the query results.

13. The computer-implemented graphical user interface of claim 1, wherein the user is allowed to select the current column data value from the query results by activating a graphical user interface control on the second interface region.

14. The computer-implemented graphical user interface of claim 1, wherein the user is allowed to select the replacement column data value by manually typing a data value.

15. The computer-implemented graphical user interface of claim 1, wherein the first interface region displays a data range selected by the user for replacement within the query results.

16. The computer-implemented graphical user interface of claim 1, wherein the first interface region displays a data range selected by the user through a graphical user interface control for replacement within the query results.

17. The computer-implemented graphical user interface of claim 1, wherein the computer-implemented graphical user interface displays in a tabular format, the current column data value and the replacement column data value for the current column data value displayed in the query results.

18. The computer-implemented graphical user interface of claim 1, wherein the computer-implemented graphical user interface displays in a tabular format, the current column data values and replacement column data values for the current column data values displayed in the query results.

19. The computer-implemented graphical user interface of claim 1, wherein the computer-implemented graphical user interface displays in a tabular format, data ranges and replacement values for the displayed data ranges.

20. The computer-implemented graphical user interface of claim 1, further comprising:

a plurality of windows, wherein the first interface region is contained on a first window and the second interface region is contained in a second window.

21. The computer-implemented graphical user interface of claim 20, wherein a wizard guides a user through the first window and the second window.

22. The computer-implemented graphical user interface of claim 1, wherein the first interface region and the second interface region are contained within a same interface window.

23. The computer-implemented graphical user interface of claim 1, further comprising:

graphical user interface controls associated with the first interface region wherein the user selects at least one data value to replace within the query results, and wherein a list of functions is provided by the first interface wherein the user is allowed to use a function with respect to a column wherein data values are subject to replacement.

24. The computer-implemented graphical user interface of claim 1, further comprising graphical user interface controls associated with the second interface region wherein the user may specify the replacement column data value.

25. The computer-implemented graphical user interface of claim 1, further comprising:

a third interface region wherein the third interface region allows the user to specify that discrete data values in the query results that are to be replaced.

26. The computer-implemented graphical user interface of claim 1, wherein the user is allowed to provide through the computer-implemented graphical user interface a discrete value which is the replacement column data value.

27. The computer-implemented graphical user interface of claim 1, wherein the user is allowed to provide through the computer-implemented graphical user interface a range for the data values which are replaced within the query results.

28. The computer-implemented graphical user interface of claim 1, wherein the user is allowed to provide through the computer-implemented graphical user interface, a range for the data values within the query results, wherein the range of data values are replaced by a replacement column data value.

29. The computer-implemented graphical user interface of claim 1, wherein the first interface region allows the user to select the current column data values from the query results,

wherein the second interface region allows the user to specify replacement column data values for the selected current column data values from the query results,

wherein the SQL CASE statement construction module generates the SQL CASE statement based upon the replacement column data values specified in the second interface region, and

wherein current column data values contained in the query results are replaced with the replacement column data values based upon execution of the generated SQL CASE statement.

30. The computer-implemented graphical user interface of claim 29, wherein the replacement column data value is a string value, and wherein the SQL CASE statement construction module generates the SQL CASE statement wherein the string value is enclosed within quotation marks.

31. The computer-implemented graphical user interface of claim 29, wherein the SQL CASE statement construction module generates column information thereby conforming to SQL CASE syntax, to append to a case term.

32. The computer-implemented graphical user interface of claim 29, wherein the SQL CASE statement construction module generates a WHEN portion to place after a case term based upon query results including the current column data values specified through the computer-implemented graphical user interface.

33. The computer-implemented graphical user interface of claim 32, wherein the SQL CASE statement construction module generates a THEN portion to place after the WHEN portion based upon the replacement column data values specified through the computer-implemented graphical user interface.

34. The computer-implemented graphical user interface of claim 33, wherein the SQL CASE statement construction module generates an ELSE portion to place after the THEN portion based upon a default value specified through the computer-implemented graphical user interface.

35. The computer-implemented graphical user interface of claim 33, wherein the SQL CASE statement construction module generates an END term to place after the WHEN portion in order to have the generated SQL CASE statement conform to SQL CASE syntax.

36. The computer-implemented graphical user interface of claim 29, wherein the SQL CASE statement construction module generates a case term based upon the replacement column data values specified through the computer-implemented graphical user interface.

37. The computer-implemented graphical user interface of claim 1, wherein the computer-implemented graphical user interface allows the user to provide through the computer-implemented graphical user interface a discrete value which is the replacement column data value for the current column data value displayed in the query results,

wherein the computer-implemented graphical user interface allows the user to provide a range of data values which are replaced by a replacement column data value, and

wherein the SQL CASE statement construction module generates an SQL CASE statement based upon the provided discrete value and the range of data values.

38. The computer-implemented graphical user interface of claim 37, wherein execution of the generated SQL CASE statement modifies data contained within the query results.

39. The computer-implemented graphical user interface of claim 1, wherein execution of the generated SQL CASE statement modifies data contained within multiple columns of the query results.

40. The computer-implemented graphical user interface of claim 1, wherein execution of the generated SQL CASE statement modifies data contained within the query results, and wherein the execution is performed by a database engine.

41. The computer-implemented graphical user interface of claim 1, wherein the generated SQL CASE statement is stored in a file.

42. The computer-implemented graphical user interface of claim 1, wherein the generated SQL CASE statement is inserted into a computer program.

43. The computer-implemented graphical user interface of claim 1, wherein computer memory contains storage locations for storing a data structure, the data structure comprising storage locations for the current column data value displayed through the first interface region and for the replacement column data value specified through the second interface region,

the SQL CASE statement construction module having a data connection to the data structure wherein the SQL CASE statement construction module generates the SQL CASE statement.

44. The computer-implemented graphical user interface of claim 1, wherein a view is created from the query results, wherein the view is a virtual table derived from tables from a relational database system, and wherein the computer-implemented graphical user interface utilizes data structure information of the tables to display at least one data value from the view.

45. The computer-implemented graphical user interface of claim 1, wherein a view is created from the query results, wherein the view is a virtual table derived from tables in a relational database system, and wherein the SQL CASE statement construction module utilizes data structure information of the tables to generate the SQL CASE statement.

46. The computer-implemented graphical user interface of claim 45, wherein the data structure information is selected from a group consisting of table column names, replacement values, column data types, and combinations thereof.

47. The computer-implemented graphical user interface of claim 1, wherein a view is created from the query results, wherein the current column data and replacement column data values corresponding to the view and specified through the computer-implemented graphical user interface, are of different data types.

48. The computer-implemented graphical user interface of claim 47, wherein the view is created from the query results, and wherein database storage requirements for current data corresponding to the view, are less than database storage requirements for the replacement data values specified through the computer-implemented graphical user interface.

49. The computer-implemented graphical user interface of claim 48, wherein the view is created from the query results, and wherein execution of the generated SQL CASE statement replaces numeric code values in the view with textual data values.

50. The computer-implemented graphical user interface of claim 1, further comprising:

a third interface region wherein the generated SQL CASE statement is may be modified prior to execution, and wherein a list of functions provided by the third interface allows the user to use a function with respect to a column specified in the generated SQL CASE statement.

51. The computer-implemented graphical user interface of claim 1, wherein a view is created from the query results, and wherein the computer-implemented graphical user interface allows the user to specify a default value to use as a replacement value when a match is not achieved between view data values specified through the computer-implemented graphical user interface and data values contained in the view.

52. A computer-implemented method for modifying data contained within query results via a Structured Query Language (SQL) CASE statement using a graphical user interface, comprising:

executing software instructions on one or more processors to display to a user at least one data value from the query results, the query results being created by an SQL SELECT statement database retrieval operation containing column parameters and filter parameters, the query results including current column data values resulting from execution of the SQL SELECT statement;

executing software instructions on the one or more processors to allow the user to specify within graphical user interface, a replacement column data value for the current column data value displayed in the query results; and

executing software instructions on the one or more processors to automatically generate an SQL CASE statement based upon the replacement column data value specified through the graphical user interface,

wherein a column data value contained in the query results is modified based upon execution of the generated SQL CASE statement.

53. The computer-implemented method of claim 52, wherein the query results are from a view.

54. The computer-implemented method of claim 53, wherein the data values contained within the view are based upon data values correspondingly contained within a database, and wherein the automatically generated SQL CASE statement modifies the data value in the view without modifying the corresponding data value in the database.

55. Computer software stored on a computer readable media, the computer software comprising program code for carrying out the computer-implemented method according to claim 52.

56. A computer-implemented system for modifying data contained within query results via a Structured Query Language (SQL) CASE statement, comprising:

a processor;

a computer-readable storage medium containing software instructions which are executed by the processor to perform operations including;

first software instructions to display to a user at least one data value from the query results, the query results being created by an SQL SELECT statement database retrieval operation containing column parameters and filter parameters, the query results including column data values resulting from execution of the SQL SELECT statement;

second software instructions to allow the user to specify within a graphical user interface a replacement column data value for a column data value from the query results, wherein the replacement column data value is a string value;

third software instructions to automatically generate an SQL CASE statement based upon the replacement column data value specified through the graphical user interface, wherein the SQL CASE statement generates the string value which is enclosed within quotation marks; and

wherein the column data value contained in the query results is modified based upon execution of the generated SQL CASE statement, without modifying a corresponding data value in a relational database.

57. A computer-implemented graphical user interface for modifying, through a Structured Query Language (SQL) CASE statement, data contained within query results, comprising:

instructions stored in a computer-readable storage medium and executed on one or more processors to generate;

a first interface region that includes a plurality of selectable column data values from the query results, the query results including column data values resulting from execution of an SQL SELECT statement, each of the selected column data values having multiple occurrences within the query results;

a second interface region that receives a replacement column data value in order to substitute a selected one of the plurality of selectable column data values of the first interface region with the replacement column data value of the second interface region;

a third interface region that displays a selectable column data value from the first interface region and a corresponding replacement column data value from the second interface region; and

an SQL CASE statement construction module for generating an SQL CASE statement based upon the replacement column data value specified in the second interface region,

wherein the replacement column data value contained in the query results is modified based upon execution of the generated SQL CASE statement, without modifying a corresponding data value in a relational database.

58. The computer-implemented graphical user interface of claim 57, further comprising a user interface control, the user interface control configured to add the selected column data value and the corresponding replacement column data value to the third interface region.