MyPage is a personalized page based on your interests.The page is customized to help you to find content that matters you the most.


I'm not curious

esProc Simplifies SQL-style Computations ΓΆ€“ Arrange Multilayered Data in One Column

Published on 10 November 14
214
0
0
We are often confronted with complicated SQL-style computations during developing data base applications. For instance, arrange grouped data into one column in certain order, making marks for different types of groups and detail data, as shown in the following:
esProc Simplifies SQL-style Computations â Arrange Multilayered Data in One Column - Image 1

This kind of operation is commonly seen in organizing report data. As SQL lacks the mechanism of ordered sets, we need to create computed columns for sorting based on group and detail data, combine the group and the detail data together and finally sort them. Usually, special SQL functions of non-ANSI standard are used to realize the operation, which complicates the code writing and produces difficult-to-understand code. The operation will be made even more difficult if we try to combine multilayered groups together.

However, we can use esProc to perform this kind of operation without creating computed columns and with concise and simple code. The method will be explained in detail through the following example.

Computing target:

Arrange the branch stores and their corresponding DVD copies in one column according to DVDCopy table.

Data Structure:

The first three fields of DVD table are CopyID, DVDID and BID, which respresent the ID numbers of DVD copies, DVDs and branch stores respectively. There is a many-to-one relationship between DVD copies and a branch store. Part of the data is as follows:
esProc Simplifies SQL-style Computations â Arrange Multilayered Data in One Column - Image 2
The computed result should be like this:
esProc Simplifies SQL-style Computations â Arrange Multilayered Data in One Column - Image 3
Code written in esProc:
esProc Simplifies SQL-style Computations â Arrange Multilayered Data in One Column - Image 4
A1ï¼ Retrieve data from the database and sort them by BID field. The result is as follows:
esProc Simplifies SQL-style Computations â Arrange Multilayered Data in One Column - Image 5

A2ï¼=create(value,type) aims to create an empty table sequence A2 having two fields: value and type.

A3-C4ï¼Traverse the data in A1 and insert BID and CopyID into A2. Detailed steps: If BID of the current record is changed (what the code in B3 represents), insert a branch store record into A2 (what the code in C3 represents); modify BID of the current record to B2 (what the code in B4 represents) in order to see if BID will change in the next record; insert a DVD copy record (what the code in C4 represents).

for A1 in the code in A3 represents traversing by loop during which one record will be fetched from A1 each time. The current record, like A3.BID, can be accessed by the variable A3 in the loop body. The working scope of a loop statement can be represented directly by the indentation of cells, such as the working scope of A3 is B3-C4.

After the traversing by loop is over, the final computed result will be stored in A2, as shown in the following figure:
esProc Simplifies SQL-style Computations â Arrange Multilayered Data in One Column - Image 6

Further discussion: Move more layers of data into one column

In the previous example, there are only two layers of data: BID and CopyID. Actually BID, DVDID and CopyID can form three-layer data. In a similar way, we can also combine the three-layer data into one column in esProc. The code is as follows:
esProc Simplifies SQL-style Computations â Arrange Multilayered Data in One Column - Image 7
Computed result is as follows:
esProc Simplifies SQL-style Computations â Arrange Multilayered Data in One Column - Image 8
In addition, esProc can be called by the reporting tool or a Java program. The method is similar to that with which a Java program calls an ordinary database. The JDBC provided by esProc can be used to return a computed result of the form of ResultSet to Java main program. For more details, please refer to the related documents
We are often confronted with complicated SQL-style computations during developing data base applications. For instance, arrange grouped data into one column in certain order, making marks for different types of groups and detail data, as shown in the following:

esProc Simplifies SQL-style Computations â

This kind of operation is commonly seen in organizing report data. As SQL lacks the mechanism of ordered sets, we need to create computed columns for sorting based on group and detail data, combine the group and the detail data together and finally sort them. Usually, special SQL functions of non-ANSI standard are used to realize the operation, which complicates the code writing and produces difficult-to-understand code. The operation will be made even more difficult if we try to combine multilayered groups together.

However, we can use esProc to perform this kind of operation without creating computed columns and with concise and simple code. The method will be explained in detail through the following example.



Computing target:



Arrange the branch stores and their corresponding DVD copies in one column according to DVDCopy table.

Data Structure:



The first three fields of DVD table are CopyID, DVDID and BID, which respresent the ID numbers of DVD copies, DVDs and branch stores respectively. There is a many-to-one relationship between DVD copies and a branch store. Part of the data is as follows:

esProc Simplifies SQL-style Computations â

The computed result should be like this:

esProc Simplifies SQL-style Computations â

Code written in esProc:

esProc Simplifies SQL-style Computations â

A1ï¼ Retrieve data from the database and sort them by BID field. The result is as follows:

esProc Simplifies SQL-style Computations â

A2ï¼=create(value,type) aims to create an empty table sequence A2 having two fields: value and type.

A3-C4ï¼Traverse the data in A1 and insert BID and CopyID into A2. Detailed steps: If BID of the current record is changed (what the code in B3 represents), insert a branch store record into A2 (what the code in C3 represents); modify BID of the current record to B2 (what the code in B4 represents) in order to see if BID will change in the next record; insert a DVD copy record (what the code in C4 represents).

for A1 in the code in A3 represents traversing by loop during which one record will be fetched from A1 each time. The current record, like A3.BID, can be accessed by the variable A3 in the loop body. The working scope of a loop statement can be represented directly by the indentation of cells, such as the working scope of A3 is B3-C4.

After the traversing by loop is over, the final computed result will be stored in A2, as shown in the following figure:

esProc Simplifies SQL-style Computations â

Further discussion: Move more layers of data into one column



In the previous example, there are only two layers of data: BID and CopyID. Actually BID, DVDID and CopyID can form three-layer data. In a similar way, we can also combine the three-layer data into one column in esProc. The code is as follows:

esProc Simplifies SQL-style Computations â

Computed result is as follows:

esProc Simplifies SQL-style Computations â

In addition, esProc can be called by the reporting tool or a Java program. The method is similar to that with which a Java program calls an ordinary database. The JDBC provided by esProc can be used to return a computed result of the form of ResultSet to Java main program. For more details, please refer to the related documents

This blog is listed under Development & Implementations and Data & Information Management Community

Post a Comment

Please notify me the replies via email.

Important:
  • We hope the conversations that take place on MyTechLogy.com will be constructive and thought-provoking.
  • To ensure the quality of the discussion, our moderators may review/edit the comments for clarity and relevance.
  • Comments that are promotional, mean-spirited, or off-topic may be deleted per the moderators' judgment.
You may also be interested in
Awards & Accolades for MyTechLogy
Winner of
REDHERRING
Top 100 Asia
Finalist at SiTF Awards 2014 under the category Best Social & Community Product
Finalist at HR Vendor of the Year 2015 Awards under the category Best Learning Management System
Finalist at HR Vendor of the Year 2015 Awards under the category Best Talent Management Software
Hidden Image Url

Back to Top