on 05-15-2013 8:14 PM
Hi All
I am trying to add a Aggregation result along with Other Projection result, but could not find how to achieve , below is example.
quotes = CE_OLAP_VIEW(_SYS_BIC.package/AN1,[quotes Attributes]);
orders = CE_OLAP_VIEW((_SYS_BIC.package/AN1,[order Attributes]);
quotes_orders =CE_JOIN(:quotes,:orders,[quoteid]);
aggr_quotes=CE_aggregation(:quotes_orders,[count(quoteid)],[status]);
var_out = CE_PROJECTION (:quotes_orders,[all the attributes]);
from the above I have to add count(quoteid ) along with other attributes from quotes_orders,I tried to join quote_orders and aggr_quotes using CE_JOIN, but there is no common attribute as I have renamed count (quoteid) as quote_count_status.
Request your ideas in achieving the above.
Thanks
Santosh
Hi Santosh,
After reading your post, i too thought that whether it is possible or not and kept painful effort but interesting
I did some workaround for your scenario and here is my approach.
If you want to include the attributes of aggregated data in addition to other attributes of joined data, we can use special operator CE_VERTICAL_UNION.
First i created 2 tables Quotes and Orders with data as shown below:
Then i created SQLScript Calculation view as shown below:
BEGIN
Q1 = CE_COLUMN_TABLE("SRK"."quotes");
O1 = CE_COLUMN_TABLE("SRK"."orders");
J1 = CE_JOIN(:Q1, :O1, ["quoteid"], ["quoteid"
,"quote_name"
,"product"
,"total_cost"
,"orderid"
,"representative"
,"order_date"
,"Order_cost"
]
);
A1 = CE_AGGREGATION (:J1,[COUNT("quoteid")
AS CNT_QID],["product"]);
var_out = CE_VERTICAL_UNION (:J1, ["quote_name"
,"representative"
,"Order_cost"],
:A1, ["CNT_QID"
,"product"]
);
END
After activating the view, here is the output:
I grouped the quote id with product instead of year.
Note: The vertical union is sensitive to the ordering of its input. SQL statements and many calculation engine plan operators may reorder their input or return their result in different orders across invocations. This may lead to unexpected results.
For more details on this operator, please refer to page 28 of SQLScript guide SPS 05.
Regards
Raj
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hi All
I dobut whether the above would be possible .But besides that I would like to understand the real use of CE_AGGREGATION in conjunction with other CE functions.
Thanks
Santosh
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
83 | |
24 | |
12 | |
9 | |
7 | |
6 | |
5 | |
5 | |
5 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.