KR20010105679A - Csv report generation - Google Patents

Abstract

A rolling bearing is used for supporting a rocking mechanism or a rotating mechanism, and a film comprising a fluorine-containing polymer having a functional group(s) under a fluidized state or a film comprising a fluorine-containing polyurethane compound under a solid state is formed on at least the track surface of the rolling bearing. Since this film does not contain ions for uniformly dispersing a thickner having non uniform particle sizes such as fluorine based grease of the prior art, breakage of the film between a rolling element and a race is not likely to occur, and wear of a rolling element race resulting from the direct contact between them due to breakage of the film can be reduced. When this film is the same kind of a film as a protective film of the surface of an information recording disk, dust generating components of the film are the same kind of components as those of the protective film, and information recording/reproduction of the information recording disk is not impeded.

Description

How to create a CV format file {CSV REPORT GENERATION}

The present invention relates to a method for creating a CSV format file.

In general, in a program for controlling a database, when a user needs to reprocess the data once retrieved through the screen, it is most often performed by calling a dedicated output program designed for this purpose.

Therefore, only the report of the already produced form can be outputted, and in order to output another form of report, a new report program is produced and used by requesting the program production department again.

However, the report program produced as described above is often created without a computer expert grasping the business content of the program, and the business user is also often ignorant about computerization. There is no choice but to go through the correction.

Moreover, even with this report, which requires a lot of time and manpower, similar reports are gradually increasing due to the modification of the aggregation method and the output form. As a result, the frequency of use is extremely low or even disposable. And the situation is a waste of money.

Therefore, the present invention provides a CSV format that can make a much wider range of reports at a convenient level by providing an intermediate step of creating a CSV-formatted file that can be read in Excel in a step for making a report in a form desired by a non-computer expert. Its purpose is to provide a way of creating a file.

1 is a conceptual diagram of the present invention.

2 is a table configuration diagram configured to output a code name to the CSV output of the present invention.

3 is a program configuration diagram of the present invention.

4 is a block control diagram for implementing the present invention.

5 is an operation flowchart according to an embodiment of the present invention.

6 is an operation flowchart according to another embodiment of the present invention.

7 is a flowchart of the overall operation of the present invention.

8 is a screen for searching after completing a search in a general program.

FIG. 9 is a screen displayed according to the operation flow of FIG. 6.

10 is a screen of reading a file output in CSV from Excel.

11 is a screen illustrating a case where a list item exists.

12 is a screen illustrating a file converted by FIGS. 10 and 11.

13 is a screen in which an administrator assigns a Hangul name to a table (including a view table) that can be used by a user.

FIG. 14 is a screen for attaching a Hangul name to a column of a table shown in FIG. 13.

15 is a table selection screen of the CSV report generator used by a general user.

FIG. 16 is a screen for selecting a column according to FIG. 15.

17 is a screen for inputting a search condition according to FIG. 15.

Explanation of symbols on the main parts of the drawings

70 ------ Search condition extraction means 80 ------ Separation means

90 ------ System table lead means 100 ----- Column display means

110 ----- Column selection means 120 ----- CSV output means

The CSV format file creation method of the present invention for achieving the above object comprises the steps of: designating, by an administrator, a national language name of a table necessary for a user to search; Designating a national language name of a column configured in the table; Registering a code value and code name for the list item; Extracting and reusing the SQL used by the user for searching; Comparing the columns on the SQL with all the columns on the actual table and displaying them on the screen; Selecting, by the user, an appropriate column for CSV conversion in the program executed by the SQL recycling step and the screen display step; Registering a table (including a view table) available to the user by the administrator; Selecting a table from which the user wants to output CSV from among the tables registered by the administrator; A user inputting a search condition for all columns of the table; A user inputting a search operator and converting the search operator into an SQL operator used in an RDBMS such as LIKE, BETWEEN, and IN; Determining whether there is a list item in the selected column and converting the same by referring to a separate code value and code name according to the determination result; Generating control SQL for CSV format conversion; If there is a date format among the selected columns, converting it; Generating an SQL statement through a conversion by the SQL conversion step and the list item conversion step; And generating a CSV format file by performing SQL through the SQL conversion step, the list item conversion step, and the SQL statement generation step.

Hereinafter, exemplary embodiments of the present invention will be described in detail with reference to the accompanying drawings.

The present invention is composed of the following two methods.

That is, (1) user extracts search conditional statement (SQL) used in inquiry through arbitrary inquiry program and outputs it in CSV, and (2) selects the kind of report that wants to output through dedicated CSV output program. After inputting the search condition, it is outputted as CSV.

To this end, the present invention allows the user 1 to create a CSV format file (2) in the step for creating a report of the desired form to be read in Excel (3).

In other words, the present invention recycles the existing search condition by pressing a button located in the tool bar of the MDI window without giving a separate search condition while leaving the screen once searched in the C / S and web environments. Create a file for

In addition, the present invention is not to create a new report when a user wants to create a report, but to create a table or view table for outputting a report, attaching a table name or a column name in Korean for convenient use, and inputting a desired condition. Create a report in CSV format that can be reused.

4 to 6, in order to output the CSV of the method (1), the user extracts the SQL used for the search using the search condition statement extracting means 70 (step S1 of FIG. 5) and separates it. After separating the used table, column and search condition using the means 80 (step S2 of FIG. 5), the system table of the database is read through the system table reading means 90 (step S3 of FIG. 5), and the column The display means 100 displays the columns used for SQL and all remaining columns defined in the table on the screen (step S4 of FIG. 5), and allows the user to select the necessary columns through the column selection means 110. Subsequently (step S5 of FIG. 5), CSV output is carried out via the CSV output means 120 (step S6 of FIG. 5).

In order to output the CSV of the method (2), the user selects a table necessary for the search (step S1 in FIG. 6), inputs a search condition (step S2 in FIG. 6), and outputs the CSV (step S3 in FIG. 6). ).

At this time, since the names of the tables or columns in both the methods (1) and (2) are mostly composed of words unknown to the general user, appropriate column names can be written in the native language.

In addition, in the case of the item defined by the list item, the stored code value and the code name are often different, so that the code name can be output together with the CSV output.

That is, in the case of the list item, the code value is a code that the user does not know, so the meaning of the report is halved when the actual database value is output. Therefore, the list item is to output the code name, not the code value.

To this end, as shown in FIG. 2, a table 10 storing Korean names of tables, a table 20 storing tables of each column, a table 30 storing code values and code names of list items, and A program 50 that manages these tables is configured.

In addition, as shown in FIG. 3, a program 60 for creating a CSV using SQL used in an arbitrary program and a program 61 for inputting a search condition for all tables and outputting the data as a CSV are configured.

The present invention separates the table used in the SQL and the search condition clause after the user has evicted the SQL used for the search for method (1).

At this time, the separated table information and conditional clause are passed to the program for the method (1). This program reads the system table (USER_TAB_COLUMNS) of the database and searches all columns specified in the usage table.

The column is again referred to by the administrator in advance by referring to the table shown in FIG. 13 and the Hangul name of the column shown in FIG.

As shown in FIG. 9, when a user selects a desired column for output and presses a create button, a desired CSV output can be obtained.

For method (2), previously registered tables and columns are selected as shown in FIG. As shown in FIG. 15, a search condition is input for CSV output. In this case, as shown in FIG. 17, various search is possible by inputting an appropriate expression.

For example, the use of expressions

Conditions starting with AAA = AAA%

Intermediate condition with AAA =% AAA%

Condition to search AAA and AAB at the same time = AAA // AAB

Condition to retrieve interval of AAZ from AAA = AAA .. AAZ

Etc. can be mentioned.

In the most widely used Oracle RDBMS, information about tables registered by database users is stored in USER_TABLES, and information about columns is stored in USER_TAB_COLUMNS. You can see all of them.

Also, the basic SQL statement for CSV output is as follows.

SET LINESIZE 1000;

SET PAGESIZE 0;

SET FEEDBACK OFF;

SET TERM OFF;

SET HEADING OFF;

SET COLSEP '","';

SPOOL xxx.csv;

SELECT '"' || column1, column2 || '"' from table_name WHERE ....; SPOOL OFF;

If the column contains a date, you need a technique to convert the date to a character type.

SELECT '"' || to_char (column1, 'yyyy-mm-dd'), column2 || '"' from table_name WHERE ....;

If a list item exists, a technique is needed to convert the code value and code name.

SELECT '"' || decode (column1, 'A', 'name1', 'B', 'name2', 'C', 'name3', null), column2 || '"' from table_name WHERE. ...;

In addition, since a title must be given to each cell of Excel due to the characteristics of Excel, a part for creating a title must be added to the front. So if you look at the overall SQL,

SET LINESIZE 1000;

SET PAGESIZE 0;

SET FEEDBACK OFF;

SET TERM OFF;

SET HEADING OFF;

SELECT 'Title 1, Title 2, ...' FROM DUAL;

TITLE OFF;

SET COLSEP '","';

SPOOL xxx.csv;

SELECT '"' || column1, column2 || '"' FROM table_name WHERE ....; SPOOL OFF;

Becomes

An example of the SQL actually used is as follows.

SET LINESIZE 1000

SET PAGESIZE 0

SET FEEDBACK OFF

SET TERM OFF

SPOOL c: \ temp \ csvrpt.csv

SET HEADING OFF

select 'enter, date input, code1, code2, code3, sequence number, product code, product classification, cost classification, quantity' from dual;

TTITLE OFF

SET COLSEP '","'

col ENTER_DATE for a19

select '"' || ENTER_BY, to_char (ENTER_DATE, 'yyyy-mm-dd hh24: mi'), CODE1, CODE2, CODE3, SEQ_NUM, ACT_NO, decode (ACT_TYPE, '0', 'regular item', '1' , 'Separate items', '2', 'preparation items', '3', 'exception items', null), decode (COST_TYPE, '1', 'material costs', '2', 'labor costs', '3' , 'Expense', null), QTY || '"' from t2govact_pum where ... order by ...;

SPOOL OFF

SET FEEDBACK ON

EXIT

The CSV output by the SQL is as follows.

Input method, input date, code 1, code 2, code 3, sequence number, product code, product classification, cost classification, quantity

"KIMS", "1999-01-01 12:00", "0042", "02", "01", "5", "11810110", "regular item", "material cost", ".3"

"KIMS", "1999-01-01 12:00", "0042", "02", "01", "4", "15860160", "Regular Item", "Material Cost", ". 00255"

"KIMS", "1999-01-01 12:00", "0042", "02", "01", "3", "15910160", "Regular Item", "Material Cost", ". 005832"

Fig. 7 is an overall operation flow chart of the present invention. First, the administrator designates a national language name of a table necessary for a user to search (step S1), a national language name of a column configured in the table (step S2), and a list. The code value and code name for the item are registered (step S3).

Thereafter, the user extracts and recycles the SQL used for the search (step S4), and compares the columns in the SQL with all the columns in the actual table and displays them on the screen (step S5).

Accordingly, the user evicts the SQL used for the search, and selects an appropriate column for CSV conversion in the program executed to compare the column on the SQL with the column on the actual table (step S6).

In this state, the administrator registers a table available to the user, but includes the view table in the table (step S7).

The user selects a table for which CSV output is desired from among the tables registered by the administrator (step S8), and inputs a search condition for all columns of the table (step S9).

Thereafter, the user inputs a search operator and converts it to an SQL operator used in an RDBMS such as LIKE, BETWEEN, or IN (step S10). YES) Conversion is performed by referring to the other code value and code name (step S12).

When conversion of the list item is completed, control SQL for CSV format conversion is generated (step S13), and it is determined whether or not there is a date format among the selected columns, and if there is a date format (YES in step S14), the date format is converted (step S13). S15).

In this state, an SQL statement is generated through the conversion process of steps S10, S11, and S12 (step S16), and the user inputs a search operator and converts it to an SQL operator used in an RDBMS such as LIKE, BETWEEN, IN, and the like. To perform the operation to find the list item in the selected column, convert it by referring to a separate code value and code name, and generate the control SQL through the steps S10, S11, S12, and S13. A file is generated (step S17).

As described above, using the present invention, it is possible to create various types of reports that can be reprocessed by the user by reusing the SQL once used for retrieval without the need for a separate program, and in particular, all tables designated by the operator ( Search conditions can be entered and output can be generated for the view table (including the view table), which can be used to create a one-time report, which is very convenient.

Claims (2)

Assigning, by the administrator, a national language name of a table necessary for the user to search; Designating a national language name of a column configured in the table; Registering a code value and code name for the list item; Extracting and reusing the SQL used by the user for searching; Comparing the columns on the SQL with all the columns on the actual table and displaying them on the screen; Selecting, by the user, an appropriate column for CSV conversion in the program executed by the SQL recycling step and the screen display step; Registering a table (including a view table) available to the user by the administrator; Selecting a table from which the user wants to output CSV from among the tables registered by the administrator; A user inputting a search condition for all columns of the table; A user inputting a search operator and converting the search operator into an SQL operator used in an RDBMS such as LIKE, BETWEEN, and IN; Determining whether there is a list item in the selected column and converting the same by referring to a separate code value and code name according to the determination result; Generating control SQL for CSV format conversion; If there is a date format among the selected columns, converting it; Generating an SQL statement through a conversion process by the SQL conversion step and the list item conversion step; And And generating a CSV format file by performing SQL through the SQL conversion step, list item conversion step, and SQL statement generation step. The name of the table or column is written in a native language, and in the case of an item defined by a list item, the code name is output when the CSV is output, since the code name is often different from the stored code value. To create a CSV format file.