cancel
Showing results for 
Search instead for 
Did you mean: 

Self Join using CE Functions

Former Member
0 Kudos

Hi All


  How can we achieve a self join functionality using CE function.Below is my select query, can we accomplish the same using CE functions.Please share your views.


select  a."order_id",b."order_id",days_between (cast (cast ( a.change_date as nvarchar)as date),cast (cast ( b.change_date as nvarchar)as date))

from "_SYS_BIC"."package/calcview" as a

INNER JOIN

"_SYS_BIC"."package/calcview" as b

ON a."order_id" = b."order_id"

where a."status" = 10 and b."status" = 20


Thanks

Santosh

Accepted Solutions (1)

Accepted Solutions (1)

lbreddemann
Active Contributor
0 Kudos

Hi Santosh,

a couple of things to keep in mind here:

  • you want to join the result of calculation views not base tables
  • the performance improvement of ce_functions derives from working very closely on the base table data structures and the ability to "cut out" calculation paths for columns that have not been requested.
    In the example above none of this can be applied.

So, what you could do is to run two CE_CALC_VIEWS + CE_PROJECTIONS functions with the filter set to status = 10 respectively status = 20, assign them to separate table variables and feed both into a CE_JOIN operation.

Finally you do another CE_PROJECTION with a CE_CALC to calculate the difference of dates - there you go!

Be aware that this couldlead to materialized intermediate result sets.

- Lars

p.s. here is what I've done:

create column table orders (order_id integer, change_date date, status integer, primary key (order_id, status));

insert into orders values (1, add_days (current_date, -2), 10);

insert into orders values (1, add_days (current_date, 2), 20);

insert into orders values (2, add_days (current_date, -1), 10);

insert into orders values (2, add_days (current_date, 4), 20);

insert into orders values (3, add_days (current_date, -11), 10);

insert into orders values (4, add_days (current_date, 3), 20);

Create a graphical calc view "CV_ORDERS" with a 1:1 mapping of the orders table to the output. All columns defined as attributes, no measures, calc view property "Mutltidimensional reporting" set to "disabled".

Create a scripted calc view with this code:

/********* Begin Procedure Script ************/

BEGIN

orders_10 = ce_calc_view ("_SYS_BIC"."lars/CV_ORDERS", ["ORDER_ID", "CHANGE_DATE", "STATUS"]);

orders_10_filtered = ce_projection (:orders_10,  ["ORDER_ID" , "CHANGE_DATE" as "CHANGE_DATE_A", "STATUS" as "STATUS_A"],

                                    '"STATUS_A" = 10' );

orders_20 = ce_calc_view ("_SYS_BIC"."lars/CV_ORDERS", ["ORDER_ID", "CHANGE_DATE"]);  

orders_20_filtered = ce_projection (:orders_10,  ["ORDER_ID" , "CHANGE_DATE" as "CHANGE_DATE_B", "STATUS"  as "STATUS_B"],

                                    '"STATUS_B"= 20' );

orders_joined = ce_join (:orders_10_filtered, :orders_20_filtered, ["ORDER_ID"],

                        ["ORDER_ID", "STATUS_A", "STATUS_B", "CHANGE_DATE_A", "CHANGE_DATE_B"] );

var_out = ce_projection (:orders_joined, ["ORDER_ID", "STATUS_A", "STATUS_B", "CHANGE_DATE_A", "CHANGE_DATE_B",

                                    CE_CALC ('daysbetween ("CHANGE_DATE_A", "CHANGE_DATE_B")', integer) AS DAYS_BETWEEN ]);

END /********* End Procedure Script ************/

Output parameters are obviously defined as ORDER_ID, STATUS_A, STATUS_B, CHANGE_DATE_A, CHANGE_DATE_B, DAYS_BETWEEN all data type integer.

Running this SQL

SELECT "ORDER_ID","CHANGE_DATE_A", "CHANGE_DATE_B", "DAYS_BETWEEN",  "STATUS_A", "STATUS_B"

FROM "_SYS_BIC"."lars/CV_ORDERS_SELFJOIN";

then delivers this result:

ORDER_IDCHANGE_DATE_ACHANGE_DATE_BDAYS_BETWEENSTATUS_ASTATUS_B
2      01.04.2013  06.04.2013  5          10      20     
1      31.03.2013  04.04.2013  4          10      20     

Which looks OKish to me.

Also the Plan Visualization doesn't deliver any absurd execution steps:

Finally, the result set of the supposedly equivalent SQL statement also looks very similar (note that no ORDERing is applied):

select

    a.order_id, a.change_date as change_date_a, b.change_date as change_date_b

            , days_between (a.change_date, b.change_date) as "DAYS_BETWEEN"

            , a.status as status_a, b.status as status_b

from orders a join orders b

   on a.order_id = b.order_id

  and a.status = 10

  and b.status = 20;

ORDER_IDCHANGE_DATE_ACHANGE_DATE_BDAYS_BETWEENSTATUS_ASTATUS_B
1      31.03.2013  04.04.2013  4          10      20     
2      01.04.2013  06.04.2013  5          10      20     

So far so good.

However, looking at the execution times for both statements, you'd probably be a bit disappointed:

--> plain SQL

successfully executed in 52 ms 209 µs  (server processing time: 9 ms 186 µs)

successfully executed in 54 ms 431 µs  (server processing time: 9 ms 497 µs)

successfully executed in 57 ms 571 µs  (server processing time: 9 ms 686 µs)

--> calc view

successfully executed in 79 ms 945 µs  (server processing time: 35 ms 208 µs)

successfully executed in 77 ms 463 µs  (server processing time: 35 ms 8 µs)

successfully executed in 79 ms 371 µs  (server processing time: 35 ms 14 µs)

As you can see, the SQL statement is quite a lot faster even for this super tiny data set.

I leave it to you to look at the admittedly more complex Plan Visualization for the SQL, but this is a good example for why it's important (performance wise) to choose the least complex processing level.

(note: I've done this on Rev. 52 - you're mileage on other revisions/with other data sets may vary)

- Lars

Former Member
0 Kudos

Hi Lars

  Thanks for Sharing your insights, as it is SQl query I was doing it on Calcview,but insdide calcview I need to implement it on internal ables,That analysis is much helpful.

But I feel this would have been simplified if we have alias definition functionality in CE_JOIN and feasibility similar to  where clause .I found it is very much needed.Any plans of including this features for CE functions.

Can you also provide your views on http://scn.sap.com/thread/3335353

Thanks

Santosh

Answers (0)