cancel
Showing results for 
Search instead for 
Did you mean: 

How to combine CE_AGGR and CE_PROJECTION

Former Member
0 Kudos

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

Accepted Solutions (1)

Accepted Solutions (1)

rindia
Active Contributor
0 Kudos

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

Former Member
0 Kudos

Hi Raj

Thanks for the response.

  Sorry for the delayed response. I will use this approach and will update you  whether it is producing the correct results or not.

Thanks

Santosh

Answers (1)

Answers (1)

Former Member
0 Kudos

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