JP2004213348A - Data base retrieving device, data base retrieving method, program, and recording medium - Google Patents

Abstract

<P>PROBLEM TO BE SOLVED: To further improve the convenience of a user in a retrieval operation. <P>SOLUTION: A server 2 stores information associated with the past data retrieval processing to a free retrieval data base 22, and at the time of executing data retrieval to the free retrieval data base 22, the retrieval processing is executed by using the stored information. Thus, the re-retrieval processing to the data base can be executed by using information associated with the past data retrieval processing to the data base, and narrowing-down retrieval can be executed without re-inputting information for retrieval. <P>COPYRIGHT: (C)2004,JPO&NCIPI

Description

[0001]
TECHNICAL FIELD OF THE INVENTION
The present invention relates to a technique for performing a data search process for a star schema type database constituted by a plurality of data tables, for example.
[0002]
[Prior art]
In a conventional database search system, a schema for executing a query (query) is generated for each program. When generating a query, knowledge of the database is always required, and a general user without knowledge must enter a numerical value or text in the preset condition parameters and execute a prepared query to search the database. , Had obtained the search results.
[0003]
However, in this case, a software developer spends a great deal of effort in generating a schema for executing a query, and the degree of freedom of search conditions and search results available to the user is small. Therefore, Japanese Patent Application Laid-Open No. 2002-117041 discloses a technique for solving those problems. As shown in FIG. 1 of the related art, in the related art, when a user specifies a table name to be queried from the display / input device 11, the initial processing unit 111 stores column information of the specified table in a file. The data is read from the device 12 via the database management unit 120. Then, the query condition setting processing unit 112 sets a query as a query condition based on the read column information. The query processing unit 113 assembles an SQL (Structured Query Language) statement from the set query and delivers it to the database management unit 120. Further, when the database access result is received from the database management unit 120, it is edited and transmitted to the display / input device 11. To display.
[0004]
Also, Japanese Patent Application Laid-Open No. 2000-187665 discloses a database system that enables an end user who does not have specialized knowledge about a database to freely derive necessary data from the database. In this prior art, a virtual file called a SOVF (Subject Oriented Virtual File) is created in order to allow a general user to easily retrieve data from a complicated relational database such as a DWH (Data Warehouse). A user who does not have knowledge of the above can easily perform data search, and automatically generates an SQL based on key information corresponding to each item set in the SOVF to perform data search.
[0005]
[Patent Document 1]
JP 2002-117041 A [Patent Document 2]
JP 2000-187665 A
[Problems to be solved by the invention]
However, in the data query system disclosed in Japanese Patent Application Laid-Open No. 2002-11741, although a user who does not understand the structure (schema) of the database can easily set search conditions, a plurality of tabular data It is not intended to generate a query when it is adapted to simultaneously search a plurality of tabular data tables in a database such as a DWH composed of tables.
[0007]
The database system disclosed in Japanese Patent Application Laid-Open No. 2000-187665 is capable of performing a data search on a database having a complicated schema. However, it is necessary to construct a relational-aware virtual file while understanding the connection key and the like, which requires a great deal of effort from the administrator.
[0008]
Further, the query generation disclosed in JP-A-2002-11741 or JP-A-2000-187665 is intended for a one-time search, and a stepwise search, for example, narrowing down search results. Cannot be easily performed. It is inconvenient to add the output condition (output item) used for the search result to the search condition again and to set the search condition again from the beginning.
[0009]
In a relational database composed of a plurality of tabular databases, for example, as disclosed in Japanese Patent Application Laid-Open No. 2000-187665, only the connection key information is used for the search condition (WHERE), and the information of the connection key is accidentally obtained. Even if the output condition is satisfied, once the search is completed, it is not left afterward, so that it is not possible to perform a search such as narrowing down or changing the output condition (output item).
[0010]
The present invention has been made in view of the above problems, and has as its object to provide a database search device, a database search method, a program, and a recording medium that can further improve the user's convenience during a search operation. And
[0011]
[Means for Solving the Problems]
In order to achieve such an object, a database search device of the present invention includes a storage unit that stores information related to past data search processing for a database, and uses the information stored in the storage unit to store the information. The search processing for the database is performed again.
[0012]
Further, the database search method of the present invention stores information related to a past data search process for a database in a storage unit, and executes the search process for the database again using the information stored in the storage unit. Features.
[0013]
A program according to the present invention causes a computer to execute the database search method. Furthermore, a computer-readable recording medium according to the present invention is characterized in that the program is recorded.
[0014]
BEST MODE FOR CARRYING OUT THE INVENTION
Hereinafter, preferred embodiments of the present invention will be described in detail with reference to the accompanying drawings. <Configuration of database search system>
FIG. 1 is a diagram schematically showing a configuration of a database search system according to one embodiment of the present invention.
Reference numeral 1 denotes a client PC operated by a user, which can display a Web page transmitted from a server using a Web browser on a screen. Reference numeral 2 denotes a server provided with a free search DB 22 (free search database) in which various data are recorded. The free search / free form application 21 (hereinafter, simply referred to as an application) searches for data in response to a request from the client PC 1. The result can be transmitted to each client PC 1 on a Web page. Reference numeral 3 denotes a system DB server, which can transmit data to be stored in the free search DB 22 to the server 2 sequentially or by batch processing.
[0015]
Each client 1 connected to the server 2 inputs desired search conditions and output data items on the screen, and transmits the input contents to the server 2. The server 2 having received the input content obtains, from the free search DB 22, data matching the search condition among the data listed in the output data item. Next, the server 2 returns the acquired data to the client PC 1 as, for example, CSV (Comma Separated Value) data, and the client PC 1 can check the search result on the screen.
[0016]
In the present embodiment, the data searched as described above can be acquired as a form. In this case, on the client PC 1, the user can specify the output format of the form. When an output format is specified on the client PC 1, the specified information is transmitted from the client PC 1 to the server 2. The server 2 searches the data required for the specified form by the function of the application 21 according to the above search conditions, generates form data, and transmits the form data to the client PC 1. Thus, the user can refer to the form data in a desired search condition and output format.
[0017]
<Main operations of the database search system>
Next, the operation of the database search system according to the present embodiment will be described in detail with reference to FIGS. 2 and 3 are flowcharts showing main operations of the database search system according to the present embodiment.
The user transmits his / her user ID and password from the client PC 1 to the server 2 by operating the client PC 1. The server 2 performs authentication processing of the user based on the user ID and password received from the client PC 1 (step S201), and when authentication is obtained, permits the user to log in to the database search system.
[0018]
Subsequently, the server 2 generates the search condition screen of FIG. 4 and transmits it to the client PC 1 (Step S202). The client PC 1 displays the search condition screen received from the server 2 (Step S102). Next, the user inputs search conditions on the search screen. At the time of input of the search condition, the user selects, for example, “product” (41 in FIG. 4) from “data classification” in the left column of the screen, and then selects the data item “product name” belonging to data classification “product” (42 in FIG. 4) is selected. Then, a small window 43 is displayed on the screen, and the user selects and inputs a search content (here, M-4 small bowl) in the small window 43. Condition designation No. With respect to No. 1, search conditions (data classification “period”, data item “year / month / day”, and search content “from 1996/12/09 to 1996/12/11”) are selected and input by the same input method.
[0019]
The above “commodity” or “period” means the data tables shown in FIGS. 20 and 21, respectively, and “commodity name” or “year / month / day” is a column name in each data table. “M-4 small bowl” and the like selected and input using are data for each column. That is, in FIG. 4, “data classification” is the selected data table, “data item” is a column, and “search content” is the content (data content) to be searched in the column. When the user specifies a data table, the server 2 refers to the table information and creates and provides column names existing in the table as display screen data. When a column name is selected, the data in the column is generated and provided as data for the display screen as shown in the small window 43.
[0020]
Condition designation No. 1, condition designation No. Every time the search condition is determined for each of the two fields, the input content is transmitted from the client PC 1 to the server 2 (step S104), and the server 2 saves the received input content each time (step S204). A new search condition screen reflecting the content is generated and transmitted to the client PC 2 (step S202).
[0021]
Subsequently, when the user presses the "data output" button (44 in FIG. 4) on the screen of FIG. 4 (step S107), information indicating that data output processing has been specified on the client PC1 is displayed on the client PC1. Is transmitted to the server 2 (step S108). The server 2 shifts the processing to step S206 according to the determination branch of step S205. On the other hand, the client PC 1 shifts the processing to step S108.
[0022]
On the server 2 side, in step S206, the output item selection screen of FIG. 5 is generated and transmitted to the client PC1. The client PC 1 receives the output item selection screen from the server 2 and displays it (step S109). The user selects a desired item to be output on the output item selection screen (step S110). The selection procedure is the same as that for inputting the search condition in FIG. In the example of FIG. 5, the output items selected by the user include the data item “year / month / day” of the data category “period”, the data item “product name” of the data category “product”, and the data item “data item” of the data category “product”. The standard selling price, the data item "standard cost" of the data category "product", and the data item "handling start date" of the data category "product".
[0023]
Also here, each time an output item is determined, the input content is transmitted from the client PC 1 to the server 2 (step S111), and the server 2 stores the received input content each time (step S208), and A new search condition screen reflecting the contents is generated and transmitted to the client PC 1 (step S206).
[0024]
When all the desired input items are confirmed, the user presses a “next” button (51 in FIG. 5) on the output item selection screen (step S112). When the “next” button is pressed by the operation of the user, the client PC 1 notifies the server 2 to that effect (step S113). The server 2 that has received the notification generates a display designation screen and transmits it to the client PC 1 (step S210). FIG. 6 shows an example of the display designation screen.
[0025]
Subsequently, the user specifies a display method for each output item on the display specifying screen (Step S115). In the example of FIG. 6, whether or not to list the data for each output item (61 in FIG. 6), the sort order of the data (ascending order, descending order; 62 in FIG. 6), and the priority of the sort process It is possible to specify (63 in FIG. 6). Here, for example, each time the display designation content of each item is determined, the input content is transmitted from the client PC 1 to the server 2 (step S116), and the server 2 saves the received input content each time (step S116). In step S212, a new search condition screen reflecting the input contents is generated and transmitted to the client PC 1 (step S210).
[0026]
Subsequently, when the user presses a "search" button (64 in FIG. 6) on the screen of FIG. 6 (step S117), information indicating that the user has input a search designation is transmitted from the client PC1 to the server. 2 (step S118). In the server 2, after the processing of step S214 via the determination branch of step S213, the processing is shifted to step S214. On the other hand, the client PC 1 shifts the processing to step S119. The query generation processing in step S214 will be described later.
[0027]
Subsequently, the application 21 receives the search result from the free search DB 22, and the server 2 transmits a search result list screen to the client PC 1 (Step S215). The client PC 1 receives the search result list screen from the server 2 and displays it (step S119). FIG. 7 shows an example of the search result list screen. In the list display specification check box in FIG. 6, all the items of the output items are checked, so that all the items are displayed in the display result.
[0028]
On the search result list screen of FIG. 7, further narrowing search of output data can be performed. When the output data is selected on the search result list screen of FIG. 7 (71 in FIG. 7) by the user's operation input (step S120), the input content is transmitted from the client PC 1 to the server 2 (step S121). ). The server 2 saves the received input content each time (step S217), generates a new search result list screen reflecting the input content, and transmits it to the client PC 1 (step S215).
[0029]
Subsequently, when the user presses a “data output” button (72 in FIG. 7) on the screen in FIG. 7 (step S122), the client PC 1 instructs the server 2 to output data from the user. Is transmitted (step S123). In the server 2, the application 21 generates a query based on the stored output data selection information and transmits it to the free search DB 22 through the determination branch of step S218 (step S219).
[0030]
Subsequently, when the application 21 receives the search result returned from the free search DB 22 in response to the query, the server 2 transmits search hit number information to the client PC 1 (step S220). The client PC 1 displays the search hit number information received from the server 2 on the screen (Step S125). FIG. 8 shows an example of the search hit number display screen. After checking the number of search hits displayed on the screen of the client PC 1 and wishing to receive the searched data as a CSV file, the user clicks the “execute” button (81 in FIG. 8) on the screen of FIG. ) Is pressed (step S126).
[0031]
When the "execute" button is pressed, information indicating that is transmitted from the client PC 1 to the server 2 (step S127). When the server 2 receives the information requesting the CSV file, the server 2 transmits the information to the client PC 1. In response, a CSV file is returned (step S222). Thereby, the user can browse the search result by the narrowed search in the CSV file.
[0032]
On the other hand, when the "form output" button (73 in FIG. 7) is pressed on the search result list screen in FIG. 7 (step S124), the client PC 1 determines that the user has specified the form output and the server 2 and the server 2 shifts the processing to step S223 by the decision branch of step S218.
[0033]
Subsequently, the server 2 transmits, for example, the form list screen shown in FIG. 9 to the client PC 1 (Step S223). The client PC 1 receives and displays the form list screen from the server 2 (Step S130). The user refers to the form list screen displayed on the client PC 1 and selects a form in a desired format. Next, the client PC 1 transmits the form selection information to the server 2 (Step S131).
[0034]
Subsequently, when the server 2 receives the form selection information from the client PC 1 (step S224), the application 21 generates a query corresponding to the form selection information and transmits it to the free search DB 22 (step S225). This query-generation will be described later. Next, when the application 21 receives the search result returned from the free search DB 22, the server 2 counts the number of search hits and transmits the result to the client PC 1 as search hit number information (step S226).
[0035]
The client PC 1 displays the search hit number information received from the server 2 (step S132). FIG. 10 shows a display example of the search hit number information. When the user wants to output the form, after confirming the search hit number information displayed on the client PC, the user presses the “execute” button (101 in FIG. 10) on the screen in FIG. 10 (step S133). . When the “execute” button is pressed, the client PC 1 notifies the server 2 that the form output execution has been specified (step S134).
[0036]
The server 2 that has received the specification of the form output execution internally transmits the search result to the form creation program (step S227), and receives the PDF data of the form from the form creation program (step S228). Then, the server 2 transmits a form screen in which the PDF data of the form and the search result data obtained from the free search DB 22 are combined to the client PC 1 (step S229). In this combination, the combining position of each column data of the form data storage table shown in FIG. 19 is determined in advance and pasted with text data or the like to the PDF data of each form, and KEY_CLM_ID in the form information storage table shown in FIG. Each sheet is synthesized as one form. The client PC 1 displays the form screen of FIG. 11 received from the server 2 (step S135). FIG. 11 illustrates a case where “store sales table by product” is selected on the form list screen of FIG. 9, and the refined search on the search result list screen of FIG. 7 is reflected. That is, in FIG. 7, the item of the date “1996/12/10” and the product name “M-4 small bowl” and the date “1996/12/11” and the product name “M-4 small bowl” are displayed. Is selected on the form screen of FIG. 11, information corresponding to them is described.
[0037]
<Search query generation (first search)>
Next, a search query generation method according to an embodiment of the present invention will be described. FIG. 12 is a flowchart showing the flow of the search query generation method according to one embodiment of the present invention, and shows the search query generation executed in step S214 of FIG. 12 and 13, a search condition storage unit 121, a display item specification storage unit 122, a relation information storage table 123, a form specification storage unit 131, a search result storage unit 132, a form information storage table 133, and a form data storage. The table 134 and the relation information storage table 135 are stored in an internal recording medium such as a RAM in the server 2.
[0038]
First, the server 2 acquires the search condition stored in step S204 of FIG. 2 from the search condition storage unit 121 (step S301). Here, the obtained search condition is used for generating a WHERE phrase of a search query described later. The search condition storage unit 121 stores a table ID corresponding to the data classification, a column ID corresponding to the data item, and a search content specified on the screen of FIG.
[0039]
In the example of FIG. In 1, the data classification “period”, the data item “year / month / day”, and the search content “from 1996/12/09 to 1996/12/11” are specified. Here, referring to the table information storage table shown in FIG. 14, the table ID corresponding to the data classification “period” is “term”. Referring to the column information storage table shown in FIG. 15, the column ID corresponding to the data item “year / month / day” is “year / month / day”. Therefore, the condition designation No. In the case of No. 1, the search condition storage unit 121 stores a table ID “term”, a column ID “year / month / day”, and a search content “1996/12/09 to 1996/12/11 (BETWEEN timestamps 1996-12-0900: 00: 00.000000 {AND timestamp {1996-12-11 23: 59: 59.9999999}) ”is stored. Similarly, condition designation No. For 2, the table ID “goods”, the column ID “product name”, and the search content “M-4 small bowl” are stored in the search condition storage unit 121.
[0040]
Next, the server 2 acquires display item designation information from the display item designation storage unit 121 (Step S302). The display item specification storage unit 122 stores output item data input on the screen of FIG. 5 and display specification data input on the screen of FIG. 6 and stored in S212. In the example of FIG. 5, as the output item data, the data item “year / month / day” of the data classification “period”, the data item “product name” of the data classification “product”, and the data item “standard selling price” of the data classification “product” The data item “standard cost” of the data category “product” and the data item “transaction start date” of the data category “product” are selected.
[0041]
In the example of FIG. 6, the data item "product name" of the data category "product" has the sort priority "1" and the sort order is specified as "ascending order", and the data item "transaction start date" of the data category "product". , The sort priority is designated as “2”, and the sort order is designated as “ascending order”.
[0042]
Therefore, the display item designation storage unit 122 stores the table ID “term” and the column ID “year / month / day”, the table ID “goods”, the column ID “product name”, the sort priority “1”, and the sort order “ascending ( ASC) ", table ID" goods "and column ID" standard selling price ", table ID" goods "and column ID" standard cost ", table ID" goods ", column ID" transaction start date ", sort priority" 2 " And the sort order “ascending order (ASC)” are stored in association with each other. The server 2 acquires the display item specification information from the display item specification storage unit 122.
[0043]
Subsequently, the server 2 acquires relation information from the relation information storage table 123 (Step S303). As shown in FIG. 16, the relation information storage table 123 includes “term”, “store”, “goods”, which are dimension tables in the star schema database according to the present embodiment, and “sls_dsgoods”, which is a main table. The relation information of is stored.
FIG. 17 shows a relational relationship of the star schema type relational database applied to the present embodiment.
[0044]
Further, tables “goods”, “term”, “stores”, and “sls_dsgoods” applicable to the present embodiment are shown in FIGS. 20, 21, 22, and 23, respectively. The table “goods” illustrated in FIG. 20 includes columns “target date”, “store code”, “product code”, “sales code”, and the like. The table “term” includes columns “year / month / day”, “year”, “month”, “day”, and the like. The table “stores” includes columns “store code”, “store name”, “scale code”, and the like. The table “sls_dsgoods” includes columns “target date”, “store code”, “product code”, “sales points”, and the like.
[0045]
Here, search items and output items are selected from the tables “term” and “goods” on the screens of FIGS. 4 and 5. Therefore, the server 2 acquires the relation information related to the tables “term” and “goods” from the relation information storage table 123. That is, the relation information acquired here includes the relation information in which the column “year / month / day” of the table “term” is associated with the column “target date” of the table “sls_dsgoods”, and the column “column of the table“ goods ”. This is relation information that associates a “product code” with a column “product code” in a table “sls_dsgoods”.
[0046]
Subsequently, in step S304, based on the relation information acquired in step S303, the server 2 generates a table relation unit that defines association between tables in a WHERE clause of the query.
Specifically, “term.date = sls_dsgoods.target date AND goods.product code = sls_dsgoods.product code” is generated as a table relation unit.
[0047]
Subsequently, the server 2 generates a WHERE clause (search condition) of the query based on the search condition acquired in step S301 and the table relation unit generated in step S304.
[0048]
Subsequently, the server generates a SELECT clause of the query based on the display item information acquired in step S302. Here, data matching the search conditions among the data belonging to the columns “year / month / day”, “product name”, “standard selling price”, “standard cost”, and “transaction start date” are stored in the tables “term”, “goods”. , "Sls_dsgoods" is required by this SELECT clause, and the columns "year / month / day" and "product code" which are the connection keys of the table "term" and "goods" selected as display items are respectively set to " It is requested by the SELEDT phrase to read back and return as "Clm_0", "Clm_1".
[0049]
Subsequently, the server 2 generates an ORDER clause of the query based on the sort priority and the sort order in the display item information acquired in step S302. That is, the data in the column "product name" of the table "goods" is sorted in the ascending order with the highest priority, and the data in the column "transaction start date" in the table "goods" is sorted in the ascending order with the next highest priority. Stipulated.
[0050]
The following SQL (Structured Query Language) statement is generated by generating the above-described query.
"SELECT DISTINCT term.date, goods.product name, goods.standard selling price, goods.standard cost, goods.transaction start date, term.date ascngClm_0, goods.product code as cngClm_1
FROM term, goods, sls_dsgoods
WHERE (term. Date = sls_dsgoods. Target date AND goods. Product code = sls_dsgoods. Product code)
AND ((term. Date BETWEEN timestamp {1996-12-09 00: 00: 00: 000000000} AND timestamp {1996-12-11 23: 59: 59.000000})
AND (goods. Trade name LIKE {% M-4 small bowl%})
ORDER BY goods. Trade name ASC, goods. Trading Start Date ASC "
[0051]
<Search query generation (at the time of refined search)>
Next, a method of generating a query in the form mode in step S225 of FIG. 3 will be described. FIG. 13 is a flowchart showing the flow of the form mode query generation method according to the present embodiment.
First, the server 2 receives the form ID received in step S224 and corresponding to the merchandise store sales table stored in the form specification storage unit 131 (step S401). Subsequently, the server 2 acquires a key column search result from the search result storage unit 132 (Step S402). The key column search result is the data of the concatenated key returned from the DB as “Clm — 0” and “Clm — 1” in step S215. Fetch key column information related to data. For example, "term. Date = timestamp '1996-12-10 23: 59: 59.999999'" or "goods. Product code = '4973307404511'" corresponds to this.
[0052]
Next, the server 2 acquires the form data information from the form information storage table 133 (Step S403). As shown in FIG. 18, the form information storage table stores form ID “REPORT_ID”, main table ID “MAIN_TBL_ID”, key column ID “KEY_CLM_ID”, overlay form name “FORM_NM”, and the like. The server 2 acquires form data information corresponding to the form ID acquired in step S401 from the form information storage table. That is, since the form ID acquired in step S401 is “803”, in the example of FIG. 18, the form ID “803”, the main table ID “goods”, the key column ID “product code”, and the overlay form name “rs_sURIAGE3. fcp "is acquired as the form data information.
[0053]
Next, the server 2 acquires form configuration item information from the form data storage table (step S404). As shown in FIG. 19, the form data storage table stores a form ID “REPORT_ID”, a table ID “TBL_ID”, a column ID “CLM_ID”, a data generation order “SORT_ORDER”, and the like. The server 2 acquires the form configuration item information corresponding to the form ID acquired in step S401 from the form data storage table. That is, the form ID acquired in step S401 is “803”, and all records in the form data storage table shown in FIG. 19 are read.
[0054]
Subsequently, the server 2 acquires relation information from the relation information storage table of FIG. 16 (Step S405). As shown in FIG. 19, the relation information acquired here includes “goods”, “store”, and “term” as tables of output items to be output as a form. Relation information is obtained. That is, table ID (TBL_ID) “term”, column ID (CLM_ID) “year / month / day”, relation table ID (CNG_ID) “sls_dsgoods”, and relation column ID (CNGCLM_ID) “target date” as the first relation information. Is obtained. As the second relation information, a table ID (TBL_ID) “store”, a column ID (CLM_ID) “store code”, a relation table ID (CNG_ID) “sls_dsgoods”, and a relation column ID (CNGCLM_ID) “store code” are acquired. . As the third relation information, a table ID (TBL_ID) “goods”, a column ID (CLM_ID) “product code”, a relation table ID (CNG_ID) “sls_dsgoods”, and a relation column ID (CNGCLM_ID) “product code” are acquired Is done.
Here, although not described in the flowchart in order to avoid repetition, the retrieval condition acquisition performed in step S301 of FIG. 12 is executed before the execution of step S406.
[0055]
Subsequently, the server 2 generates a table relation unit from the relation information acquired in Step S405 (Step S406). Here, as an indication that the tables “goods”, “store”, and “term” are linked to the table “sls_dsgoods” by their respective concatenated keys, “term.date = sls_dsgoods.target-date AND-goods .Commodity code = sls_dsgoods.commodity code AND store.store code = sls_dsgoods.store code ”is generated as a table relation unit.
[0056]
Subsequently, the server 2 generates a WHERE phrase indicating a search condition (step S407). Here, in addition to the search condition stored in the search condition storage unit in step S204 of FIG. 2 and the table relation unit generated in step S406, search result key information (link key) acquired from the search result storage unit in step S402 Here, "timestamp @ 1996-12-10 23: 59: 59.99999" for the first output item "year / month / date: 1996/12/10, trade name: M-4 small bowl" selected in step S120. $ "And" 497307404511 ", and" timestamp $ 1996-12- "for the second output item" year / month: December 11, 1996, trade name: M-4 Kobachi "also selected in step S120. 11 23: 59: 59.999999 ｀ ”,“ 4 The WHERE phrase is generated based on the data of 973307404111 ".
[0057]
Subsequently, the server 2 generates a SELECT phrase indicating a display item. Here, the columns “product code”, “product name”, “scale name”, “store code”, “store name”, “year” based on the form configuration item information acquired from the form data storage table 134 in step S404. “Date”, “Standard selling price”, “Standard cost”, “Sales points”, and “Sales price” are obtained as display items from the tables “term”, “goods”, “store”, and “sls_dsgoods” and displayed. Is specified in the SELECT clause.
[0058]
Subsequently, the server 2 generates an ORDER phrase indicating the sort order. Here, based on the form configuration item information “SORT_ORDER” and “AD_CLASS” acquired in step S404, the priority order of the column “product code” of the table “goods” and the column “store code” of the table “store” are respectively set. Are sorted in ascending order.
[0059]
The query generated in this way is as follows.
“SELECT DISTINCT goods. Product code, goods. Product name, store. Scale name, store. Store code, store. Store name, term. Date, goods. Sales price FROM term, goods, store, sls_dsgoods
WHERE ((term. Date BETWEEN timestamps 1996-12-09 00: 00: 00: 0.000000 {AND (goods. Brand name LIKE {% M-4 small bowl%}))
AND ((term. Date = timestamp {1996-12-10 23: 59: 59.9999999} AND goods. Product code = {497330740511})
OR (term. Date = timestamp {1996-12-11 23: 59: 59.9999999})
AND goods. Product code = {497307404511})
AND (term.date = sls.ds_goods.Product code AND goods.Product code = sls_dsgoods.Product code AND store.Store code = sls_dsgoods.Store code ORDER BYgoods.A.S.A code.
[0060]
As described above, in the query generated by the refined search, the search result for the link key of the table including the previous output item is reflected as a new search condition (term. Date = timestampｔ1996−). 12-10 23: 59: 59.9999999 {AND goods. Product code = {497330740511}) OR (term. Date = timestamp {1996-12-11 23: 59: 59.99999999}). For example, in the previous (first) search, "product code" is not specified as an output item or a display item, but is included in the SELECT clause as a data output condition to the free search DB 22. Is made possible by storing. In other words, in step S215, the link key is obtained and stored from the free DB 22 separately from the data output items and the display items as "Clm_0" and "Clm_1", thereby preparing for the subsequent search query generation. Further, in the query-generation in step 204 described in detail with reference to FIG.
“SELECT DISTINCT term.date, goods.brand name, goods.standard selling price, goods.standard cost, goods.transaction start date FROM term, goods, sls_dsgoods
WHERE (term. Date = sls_dsgoods. Target date AND goods. Product code = sls_dsgoods. Product code)
AND ((term. Date BETWEEN timestamp {1996-12-09 00: 00: 00: 000000000} AND timestamp {1996-12-11 23: 59: 59.000000})
AND (goods. Trade name LIKE {% M-4 small bowl%})
ORDER BY goods. Trade name ASC, goods. Trading Start Date ASC "
It is possible to create the display of the search result shown in FIG. According to the related art, an SQL sentence is generated in this way. However, according to the present invention, in order to perform a further refined search on a database having a schema that is complicatedly linked by a link key, and to enable the data to be combined into various forms having various output items, the search results are described above. The following query-generation is performed.
[0061]
As a result, it is possible to execute a refined search by inputting only new refined conditions, without having to look at the previous output items at the time of the refined search and reflecting it in the search conditions again to perform a re-search. And the search operation becomes easy for the user. Similarly, the search result for the link key is reflected in the new search condition, so that the output item in the table including the previous output item can be easily changed without reflecting the previous output item. Become. Further, a user having no knowledge of the database can easily perform a search with a high degree of freedom on a database of a complex schema having a plurality of tabular data tables.
[0062]
That is, even in the search of a database having a complicated schema, not only the selection of “data output” in FIG. 7 but also the flexibility of giving the user the flexibility to cope with a change in the data output item such as “form output” can be given. The degree of freedom is incomparable with that of the related art in which the output form can be specified only before the execution of the search or can be performed only in a predetermined manner.
[0063]
Note that it is determined in steps S216 and S217 whether all data has been selected in the data output selection (step S121) of FIG. 7 at the time of generating a query in the second search. If all data is to be output, it takes time to generate a query and interpret the query in the free search DB 22. Therefore, for all data, (term. Date = timestamp @ 1996-12) -10 23: 59: 59.99999999 {AND goods. Product code = {497330704511}) OR (term.date = timestamp {1996-12-11 23: 59: 59.99999999}) is not created. In addition, in the processing in step S407, it is possible to perform an efficient search by ignoring the output data selection condition saved in step S217 and generating a query.
[0064]
Although a specific description of a search query generation method at the time of a narrowed search for a CSV file has been omitted, a search query is generated by the same processing as that at the time of form creation, and the same effect as described above can be obtained. It is.
[0065]
FIG. 24 is a diagram illustrating a hardware configuration of the server 2 in the present embodiment.
The CPU 201 generally controls each device connected to the system bus 204. In the ROM 203 or the external memory 211, an operating system program (hereinafter, referred to as an OS), which is a control program of the CPU 201, and various programs to be described later necessary for realizing the functions executed by each server or each PC are provided. It is remembered. The RAM 202 functions as a main memory, a work area, and the like for the CPU 201.
[0066]
A keyboard controller (KBC) 205 controls key input from a keyboard 209 or a pointing device (not shown). A CRT controller (CRTC) 206 controls display on a CRT display (CRT) 210. These are used by the administrator as needed. It is not directly related to the present invention.
[0067]
An external memory controller (MC) 207 includes a boot program, browser software, various applications, font data, a user file, an edit file, a database management system (DBMS), a hard disk (HD) for storing various data, and a flexible disk (FD). ) Or controls access to an external memory 211 such as a compact flash (R) memory connected to a PCMCIA card slot via an adapter.
[0068]
The communication I / F controller 208 connects to an external device via a network, and executes communication control processing on the network. For example, Internet communication using TCP / IP is possible. Note that the CPU 201 enables the display on the CRT 210 by executing, for example, an outline font developing (rasterizing) process on a display information area in the RAM 202. Further, the CPU 201 enables a user instruction with a mouse cursor (not shown) on the CRT 210. The programs related to the respective processes described in the above-described operation flowchart and the free search DB 22 are stored in the external memory 211, and various programs are read out to the RAM 202 as necessary and executed by the CPU 201.
[0069]
Further, an object of the present invention is to provide a storage medium storing a program code of software for realizing the functions of the above-described embodiments to a system or an apparatus, and a computer (or CPU or MPU) of the system or the apparatus to store the storage medium. Needless to say, this can also be achieved by reading and executing the program code stored in the program.
[0070]
In this case, the program code itself read from the storage medium realizes the function of the above-described embodiment, and the program code itself and the storage medium storing the program code constitute the present invention.
[0071]
As a storage medium for supplying the program code, for example, a flexible disk, a hard disk, an optical disk, a magneto-optical disk, a CD-ROM, a CD-R, a magnetic tape, a nonvolatile memory card, a ROM, and the like can be used.
[0072]
When the computer executes the readout program code, not only the functions of the above-described embodiments are realized, but also the OS or the like running on the computer performs actual processing based on the instructions of the program code. It goes without saying that a case where some or all of the functions are performed and the functions of the above-described embodiments are realized by the processing is also included.
[0073]
Further, after the program code read from the storage medium is written into a memory provided in a function expansion board inserted into the computer or a function expansion unit connected to the computer, the function is executed based on the instruction of the program code. It goes without saying that a CPU or the like provided in the expansion board or the function expansion unit performs part or all of the actual processing, and the processing realizes the functions of the above-described embodiments.
[0074]
【The invention's effect】
According to the present invention, the search processing for the database is configured to be re-executable using the information related to the past data search processing for the database. When performing the necessary search processing, the user is prevented from having to refer to a previously searched screen in order to re-enter information relating to the past search processing, and the user's convenience at the time of the search operation is reduced. Can be improved.
[Brief description of the drawings]
FIG. 1 is a diagram schematically showing a configuration of a database search system according to an embodiment of the present invention.
FIG. 2 is a flowchart showing main operations of a database search system according to one embodiment of the present invention.
FIG. 3 is a flowchart showing main operations of the database search system according to one embodiment of the present invention.
FIG. 4 is a diagram showing an example of a search condition screen.
FIG. 5 is a diagram showing an example of an output item selection screen.
FIG. 6 is a diagram showing an example of a display designation screen.
FIG. 7 is a diagram showing an example of a search result list screen.
FIG. 8 is a diagram showing an example of a search hit number display screen.
FIG. 9 is a diagram showing an example of a form list screen.
FIG. 10 is a diagram showing an example of a search hit number display screen.
FIG. 11 is a diagram showing an example of a form screen.
FIG. 12 is a flowchart illustrating a flow of a search query generation method according to an embodiment of the present invention.
FIG. 13 is a flowchart illustrating a flow of a search query generation method according to an embodiment of the present invention.
FIG. 14 is a diagram showing a configuration example of a table information storage table.
FIG. 15 is a diagram illustrating a configuration example of a column information storage table.
FIG. 16 is a diagram illustrating a configuration example of a relation information storage table.
FIG. 17 is a diagram showing a relational relationship of a star schema type relational database applied to an embodiment of the present invention.
FIG. 18 is a diagram showing a configuration example of a form information storage table.
FIG. 19 is a diagram showing a configuration example of a form data storage table.
FIG. 20 is a diagram illustrating a configuration example of a table “goods”.
FIG. 21 is a diagram illustrating a configuration example of a table “term”.
FIG. 22 is a diagram illustrating a configuration example of a table “stores”.
FIG. 23 is a diagram illustrating a configuration example of a table “sls_dsgoods”.
FIG. 24 is a diagram illustrating a hardware configuration of a server according to an embodiment of the present invention.
[Explanation of symbols]
1: Client PC
2: Server 3: System DB server 21: Free search / free form application 22: Free search DB
121: search condition storage unit 122: display item specification storage unit 123: relation information storage table 131: form specification storage unit 132: search result storage unit 133: form information storage table 134: form data storage table 135: relation information storage table

Claims (10)

Having storage means for storing information relating to past data search processing for the database,
A database search apparatus, wherein a search process for the database is performed again using the information stored in the storage unit. The database search device according to claim 1, wherein the database is a relational database including a plurality of data tables. The information according to the past data search process is a search result at the time of the data search process of a column corresponding to a concatenated key of a data table that is a search target of the data search process. A database search device as described. Furthermore, it has an instruction receiving means for receiving a column instruction as a search condition and a data output item from the user,
2. The information related to the data search process is information in a column obtained separately from a column of a data output item received by the instruction receiving unit in the past data search process. The database search device according to any one of claims 1 to 3. 4. The database search device according to claim 1, wherein a search process is performed on the database by applying information related to the data search process to a part of a search query. 5. The past database search process is a search for narrowing down data, and if all data relating to the search results of the past database search process is to be the next search target, information related to the data search process The database search device according to claim 5, wherein? Is not applied to query-generation. The database search device according to any one of claims 2 to 6, wherein the relational database is a star schema type relational database. A database search method, wherein information relating to a past data search process for a database is stored in a storage unit, and a search process for the database is executed again using the information stored in the storage unit. A program for causing a computer to execute the database search method according to claim 8. A computer-readable recording medium on which the program according to claim 9 is recorded.

Images