on 04-02-2013 3:29 AM
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
Hi Santosh,
a couple of things to keep in mind here:
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_ID | CHANGE_DATE_A | CHANGE_DATE_B | DAYS_BETWEEN | STATUS_A | STATUS_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_ID | CHANGE_DATE_A | CHANGE_DATE_B | DAYS_BETWEEN | STATUS_A | STATUS_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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
User | Count |
---|---|
84 | |
25 | |
12 | |
9 | |
6 | |
6 | |
5 | |
5 | |
4 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.