category:Uncategorized

esProc Divides Letters into Segments

The RBP_VALUE field in the rbd_dtl table stores the string “all” and some letters. We want to convert values in the field to strings of segments. The desired result is A-F,H,J-K,N-O,Q-S,U-V,X-Z. Below is the original data: Rules to be followed are: If RBP_VALUE= “ALL”, then discard the value; According to the alphabet...

2015-03-17 1 0 0

esProc Simplifies SQL-style Computations – Record Splitting

Programmers often encounter complicated SQL-style computations during database application development. Record splitting, for example, is to split a record separated by a certain separator into multiple ones. For different databases, SQL has various problems like poor syntax support and the need of writing nested and m...

2015-03-13 1 0 0

esProc Codes Column Shuffling

In database table REF_VALUES, ID column acts as the primary key and ORIGINAL_VALUE is used as the original column, whose values are to be shuffled and updated to the empty SHUFFLED_VALUE column. Below is a selection from the table:  SQL approach: Databases differ in their solution to the problem. Here Oracle is u...

2015-03-12 1 0 0

esProc Codes Dynamic MERGE statement

Databases, such as MSSQL and ORACLE, support updating tables using MERGE statement. But they lack functions for performing set operations. If data structure of the target table is unknown, it is very complicated to use the stored procedure to get its data structure and then compose the dynamic SQL statement. This may n...

2015-03-11 2 0 0

esProc Performs Dynamic Cross-database MERGE Operation

The MERGE statement provided by databases like MSSQL and ORACLE is very convenient for updating tables. But it is not as convenient as it is expected to be when the source table and target table exist in different databases. In this case esProc is able to rise to the occasion and assists the operation. source and targe...

2015-03-10 1 0 0

esProc Assists BIRT to Dynamically Insert Subtable Fields into Primary Table

Database table dColThread is the primary table with the primary key tID. dColQuestion is the subtable with the foreign key tID, as shown in the following figures: dColThread dColQuestion The desired report will display data in the grid in which the primary table is presented according to ApplicationName. Each record ...

2015-03-09 1 0 0

esProc Assists Report Development – Irregular Cross Row Calculation

During data reporting there are many unconventional statistical tasks that are difficult to be handled solely by reporting tools, like Jasper and BIRT, or SQL. One case is to specify a particular record in the original data against which we cross-calculate all other records and derive new field values like percentages ...

2015-03-04 1 0 0

esProc Helps with Computation in MongoDB – Digital Comparison

MongoDB script has limited computational ability in realizing complicated operations, so it is difficult to solve problems of this kind using it alone. In many cases, you can only perform further computations after retrieving the desired data out. And there is no less difficulty in trying to realizing this kind of set ...

2015-03-03 1 0 0

esProc Assists Report Development – Inter-row Calculation

Unconventional statistical tasks are not uncommon in creating reports with reporting tools like Jasper and BIRT. One of the cases is to display the result of certain comparisons between the current record and the next/previous record. It is difficult to handle it using only the reporting tool or the SQL. Yet esProc can...

2015-02-13 1 0 0

esProc Assists Report Development – Create a Tabular Report by Transposition

Unconventional statistical tasks are difficult to be handled solely by the reporting tool like Jasper and BIRT, or the SQL. For example when the original data are not arranged as required by the tabular report, they need to be transposed for display. However, with the assistance of esProc that has a powerful computing ...

2015-02-12 1 0 0

esCalc Solves Spreadsheet Problems – Sort Detailed Data

Sometimes you are required to sort the detailed data in a spreadsheet. However, the sorting is difficult to realize due to the inflexible operation in Excel. You have to sort the data by groups, which requires quite a lot of work. But you can perform the operation more easily in esCalc. Let’s look at an example. Summar...

2015-02-04 1 0 0

esCalc Solves Spreadsheet Problems – Process Grouped Data

In Excel, groups, as well as summary area and detail area, are independent of each other. There is no inherent operational correlation between cells, so it is difficult to process grouped data. We usually need to write a great many formulas repeatedly, or process every group of data separately, like what is done for so...

2015-01-28 2 0 0

esCalc Solves Spreadsheet Problems – Filter Group Data

Excel cannot distinguish summary data from detail data due to the lack of inherent operational correlation between cells. Therefore the operation of grouping and filtering data always produces meaningless data. In order to correct the mistake and remedy this defect of Excel, users have to modify the formula for each gr...

2015-01-21 1 0 0