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 ΓΆ€“ Ungrouping

Published on 11 December 14
264
0
0

During database application development, we often need to handle complicated SQL-style computations. One of them is the ungrouping, that is, the opposite process of grouping and summarizing which requires, for example, splitting each record into multiple records. We cannot realize the operation in SQL without taking great trouble. The code is so complicated and difficult to understand.

By contrast, esProc provides easier way and thus simpler code for realizing the ungrouping. The following are two examples.

In packGather, a table that holds the summarized data of packages of various products, productID field represents the ID numbers of products, packing field represents the number of each productâs packages, all of which have the same number of products, and quantitySum represents the total quantity of a product. Some of the data are as follows:
esProc Simplifies SQL-style computations â Ungrouping - Image 1
We are asked to split packGather into tables of packages, that is, list packages separately and number each of them. Take product b as an example, there are 3 records in the table after it is split.
esProc Simplifies SQL-style computations â Ungrouping - Image 2
The following esProc code can be used to solve the problem:
esProc Simplifies SQL-style computations â Ungrouping - Image 3
A1ï¼Select all the data from the database. They are as follows:
esProc Simplifies SQL-style computations â Ungrouping - Image 4

A2= A1.conj(packing.new(~:seq, productID:product, quantitiySum/packing:quan))

This line of code first splits each record in A1 into multiple records to create two-dimensional tables. Every two-dimensional table has different number of records but the same structure, with three fields: seq, product and quantity. Then it concatenates these two-dimensional tables to form a general two-dimensional table.

The function of conj function is to concatenate data. For example, split the first record in A1. The corresponding code and result are as follows:

A1(1).(packing.new( ~:seq,productID:product,quantitiySum/packing:quan))
esProc Simplifies SQL-style computations â Ungrouping - Image 5
A1.conj(â¦) is equal to [A1(1),A1(2),A1(3)â¦].conj(â¦). The final result is as follows:
esProc Simplifies SQL-style computations â Ungrouping - Image 6

Please note the expression packing.new(â¦), which means creating a new table sequence according to the packing field of each record in A1. new function is used to create a new table sequence based on an existing sequence or table sequence, like ["a","b","c"].new() or [1,2,3â¦N].new(). The latter can be abbreviated to N.new(). If, for example, the value of packing field in the first record is 2, this expression will be parsed as [1,2].new(â¦). While creating a new table sequence using new function, we can use â~â to represent members of the original sequence. So ~:seq in the expression in A2 means using the original sequence as the first field of the new table sequence, with seq being the field name.

A2 represents the final result of this example.

Now letâs look at another example that computes liquidated damages in a database. Here is a table â contract â that has multiple fields, three of which are ID (contract number), enddate (ending date) and amount (contract amount). Please compute how much liquidated damages should be paid each day for each breach of contract, on the assumption that the required liquidated damages per day is one thousandth of the contract amount.

Some of the data of contract are as follows:
esProc Simplifies SQL-style computations â Ungrouping - Image 7
esProc code:
esProc Simplifies SQL-style computations â Ungrouping - Image 8
This piece of code uses periods function to generate a sequence of dates starting from the ending date of a contract to the current date. # represents the current sequence number in the time sequence. The final result is as follows:
esProc Simplifies SQL-style computations â Ungrouping - Image 9
Note: esProc program can be called by a reporting tool or a Java program in a way similar to that in which a Java program calls an ordinary database. The JDBC provided by esProc can be used to return a result of the form of ResultSet to the Java main program. Please refer to the related documents for details.
During database application development, we often need to handle complicated SQL-style computations. One of them is the ungrouping, that is, the opposite process of grouping and summarizing which requires, for example, splitting each record into multiple records. We cannot realize the operation in SQL without taking great trouble. The code is so complicated and difficult to understand.

By contrast, esProc provides easier way and thus simpler code for realizing the ungrouping. The following are two examples.

In packGather, a table that holds the summarized data of packages of various products, productID field represents the ID numbers of products, packing field represents the number of each productâs packages, all of which have the same number of products, and quantitySum represents the total quantity of a product. Some of the data are as follows:

esProc Simplifies SQL-style computations â

We are asked to split packGather into tables of packages, that is, list packages separately and number each of them. Take product b as an example, there are 3 records in the table after it is split.

esProc Simplifies SQL-style computations â

The following esProc code can be used to solve the problem:

esProc Simplifies SQL-style computations â

A1ï¼Select all the data from the database. They are as follows:

esProc Simplifies SQL-style computations â

A2= A1.conj(packing.new(~:seq, productID:product, quantitiySum/packing:quan))

This line of code first splits each record in A1 into multiple records to create two-dimensional tables. Every two-dimensional table has different number of records but the same structure, with three fields: seq, product and quantity. Then it concatenates these two-dimensional tables to form a general two-dimensional table.

The function of conj function is to concatenate data. For example, split the first record in A1. The corresponding code and result are as follows:



A1(1).(packing.new( ~:seq,productID:product,quantitiySum/packing:quan))

esProc Simplifies SQL-style computations â

A1.conj(â¦) is equal to [A1(1),A1(2),A1(3)â¦].conj(â¦). The final result is as follows:

esProc Simplifies SQL-style computations â

Please note the expression packing.new(â¦), which means creating a new table sequence according to the packing field of each record in A1. new function is used to create a new table sequence based on an existing sequence or table sequence, like ["a","b","c"].new() or [1,2,3â¦N].new(). The latter can be abbreviated to N.new(). If, for example, the value of packing field in the first record is 2, this expression will be parsed as [1,2].new(â¦). While creating a new table sequence using new function, we can use â~â to represent members of the original sequence. So ~:seq in the expression in A2 means using the original sequence as the first field of the new table sequence, with seq being the field name.

A2 represents the final result of this example.

Now letâs look at another example that computes liquidated damages in a database. Here is a table â contract â that has multiple fields, three of which are ID (contract number), enddate (ending date) and amount (contract amount). Please compute how much liquidated damages should be paid each day for each breach of contract, on the assumption that the required liquidated damages per day is one thousandth of the contract amount.

Some of the data of contract are as follows:

esProc Simplifies SQL-style computations â

esProc code:

esProc Simplifies SQL-style computations â

This piece of code uses periods function to generate a sequence of dates starting from the ending date of a contract to the current date. # represents the current sequence number in the time sequence. The final result is as follows:

esProc Simplifies SQL-style computations â

Note: esProc program can be called by a reporting tool or a Java program in a way similar to that in which a Java program calls an ordinary database. The JDBC provided by esProc can be used to return a result of the form of ResultSet to the Java main program. Please refer to the related documents for details.

This blog is listed under Development & Implementations Community

Related Posts:
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