Computer-implemented data replacement graphical user interface system and method

• US 20040002941A1
• Filed: 06/28/2002
• Published: 01/01/2004
• Est. Priority Date: 06/28/2002
• Status: Active Grant

• Current Assignee: SAS Institute Incorporated
• Original Assignee: SAS Institute Incorporated
• Inventors: Thorne, Greg M., Pirrello, Kelly D.
• Granted Patent: US 7,634,460 B2
• Time in Patent Office: Days
• US Class Current: 707/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, said query results resulting from execution of an SQL database retrieval operation, comprising:

a first interface region that displays at least one data value from the query results;

a second interface region for allowing a user to specify a replacement data value for the displayed data value from the query results,

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

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

2. The interface of claim 1 wherein the query results contain data derived from a relational database system.

3. The 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 interface of claim 1 wherein the data values contained within the query results are based upon data values correspondingly contained within a database,

wherein the generated SQL CASE statement modifies the data value in the query results without modifying the corresponding data value in the database.

5. The 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,

wherein the generated SQL CASE statement modifies the data value in the view without modifying the corresponding data value in the database.

6. The 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 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 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 interface of claim 1 wherein a user is allowed to select within the first interface region current values associated with a column in the query results.

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

11. The interface of claim 1 wherein the first interface region displays a current data value selected by a user for replacement within the query results.

12. The interface of claim 1 wherein the displayed data value from the query results is part of a data range selected by a user for replacement within the query results.

13. The interface of claim 1 wherein the user is allowed to select the data value from the query results by activating a graphical user interface control on the interface.

14. The interface of claim 1 wherein the user is allowed to select the replacement data value by manually typing a data value.

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

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

17. The interface of claim 1 wherein the interface comprises an interface region that displays in a tabular format a current data value and a replacement value for the displayed current data value in the query results.

18. The interface of claim 1 wherein the interface comprises an interface region that displays in a tabular format current data values and replacement values for the displayed current data values in the query results.

19. The interface of claim 1 wherein the interface comprises an interface region that displays in a tabular format data ranges and replacement values for the displayed data ranges.

20. The 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 interface of claim 20 wherein a wizard guides a user through the first window and the second window.

22. The interface of claim 1 wherein the first interface region and the second interface region are contained within a same interface window.

23. The interface of claim 1 further comprising graphical user interface controls associated with the first interface region so that a user may select at least one original data value to be replaced within the query results, wherein a list of functions is provided by the first interface so that the user is allowed to use a function with respect to a column whose data values are subject to replacement.

24. The interface of claim 1 further comprising graphical user interface controls associated with the second interface region so that a user may specify the replacement data value for the displayed data value from the query results.

25. The interface of claim 1 further comprising an interface region wherein the interface allows the user to specify that discrete data values in the query results are to be replaced.

26. The interface of claim 1 wherein a user is allowed to provide through the interface a discrete value to be the replacement data value for the displayed data value from the query results.

27. The interface of claim 1 wherein the user is allowed to provide through the interface a range for the data values to be replaced within the query results.

28. The interface of claim 1 wherein the user is allowed to provide through the interface a range for the data values to be replaced by a replacement value within the query results.

29. The interface of claim 1 wherein the first interface region allows the user to select data values from the query results,

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

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

wherein data values contained in the query results are replaced based upon the generated SQL CASE statement.

30. The interface of claim 29 wherein the replacement data value is a string value, wherein the SQL CASE statement construction module generates the SQL CASE statement so that the string value is enclosed within quotation marks.

31. The interface of claim 29 wherein the SQL CASE statement construction module generates a CASE term based upon replacement data values specified through the interface.

32. The interface of claim 31 wherein the SQL CASE statement construction module generates column information to append to the CASE term that conforms to SQL CASE syntax.

33. The interface of claim 31 wherein the SQL CASE statement construction module generates a WHEN portion to place after the CASE term based upon query results data values specified through the interface.

34. The interface of claim 33 wherein the SQL CASE statement construction module generates a THEN portion to place after the WHEN portion based upon replacement data values specified through the interface.

35. The interface of claim 34 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 interface.

36. The interface of claim 34 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.

37. The interface of claim 1 wherein the interface allows a user to provide through the interface a discrete value to be the replacement data value for the displayed data value from the query results, wherein the interface allows the user to provide a range for the data values to be replaced by a value within the query results,

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

38. The interface of claim 37 wherein execution of the generated SQL CASE statement modifies data contained within the query results.

39. The interface of claim 1 wherein execution of the generated SQL CASE statement modifies data contained within multiple columns of the query results.

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

41. The interface of claim 1 wherein the generated SQL CASE statement is allowed to be stored in a file.

42. The interface of claim 1 wherein the generated SQL CASE statement is allowed to be inserted in a computer program.

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

said SQL CASE statement construction module having a data connection to the data structure so that the SQL CASE statement construction module may generate the SQL CASE statement.

44. The 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, wherein the interface utilizes data structure information of the tables to display at least one data value from the view.

45. The 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, wherein the SQL CASE statement construction module utilizes data structure information of the tables to generate the SQL CASE statement.

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

47. The interface of claim 1 wherein a view is created from the query results, wherein the view'"'"'s current data and replacement data values specified through the interface are of different data types.

48. The interface of claim 47 wherein a view is created from the query results, wherein database storage requirements for the view'"'"'s current data are less than database storage requirements for the replacement data values specified through the interface.

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

50. The interface of claim 1 further comprising a third interface region wherein the generated SQL CASE statement may be modified prior to execution, wherein a list of functions is provided by the third interface so that the user is allowed to use a function with respect to a column specified in the generated SQL CASE statement.

51. The interface of claim 1 wherein a view is created from the query results, wherein the interface allows a user to specify a default value to be used as a replacement value when a match is not achieved between view data values specified through the 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, comprising the steps of:

displaying to a user at least one data value from the query results;

allowing a user to specify within a graphical user interface a replacement data value for the displayed data value from the query results,

automatically generating an SQL CASE statement based upon the replacement data value specified through the graphical user interface;

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

53. The method of claim 52 wherein the query results are from a view.

54. The method of claim 53 wherein the data values contained within the view are based upon data values correspondingly contained within a database,

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 a method according to claim 52.

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

graphical user interface means for displaying to a user at least one data value from the view;

graphical user interface means for allowing a user to specify a replacement data value for the displayed data value from the view,

wherein an SQL CASE statement is to be automatically generated based upon the specified replacement data value in order to modify a data value contained in the view.