on 11-20-2012 4:28 PM
Hi @ all,
we have the following sql statement in a calculation view.
var_out = select vbep.mandt, vbep.vbeln, vbep.posnr, max(left(mbdat,6)) as mbdatmax, sum(bmeng) as bmeng, vbep.lifsp
from "FP3040"."VBEP" vbep
group by vbep.mandt,
vbep.vbeln,
vbep.posnr,
vbep.lifsp
Would it be possible to receive the same result with CE_Functions?
We tried the following but it seems that the combination of max(left()) is not feasable within CE_AGGREGATE().
var_table = CE_COLUMN_TABLE("FP3040"."VBEP", ["MBDAT", "BMENG", "MANDT", "VBELN", "POSNR", "LIFSP"]);
var_out = CE_AGGREGATION(:var_table, [max(left("MBDAT", 6)), sum("BMENG")],["MANDT","VBELN","POSNR","LIFSP"]);
Thanks a lot for you input.
Flo
Hi Florian,
You can do almost everything on CE but it's normally needed more instructions (One SQL can sometimes require 6 or more steps to achieve same result), try the projection below for your case:
var_table = CE_COLUMN_TABLE("FP3040"."VBEP", ["MBDAT", "BMENG", "MANDT","VBELN", "POSNR", "LIFSP"]);
var_proj = CE_PROJECTION(:var_table,
["MBDAT","BMENG","MANDT","VBELN","POSNR","LIFSP",
CE_CALC(' leftstr("MBDAT",6) ',VARCHAR(6)) as "PERIOD"]);
var_out = CE_AGGREGATION(:var_table, [max("PERIOD"), sum("BMENG")],["MANDT","VBELN","POSNR","LIFSP"]);
Regards, Fernando Da Rós
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hi Fernando,
thank you for your helpful answer.
I guess in your statement was a little typo. I corrected the statement in the following way (marked below in red):
var_table = CE_COLUMN_TABLE("FP3040"."VBEP", ["MBDAT", "BMENG", "MANDT","VBELN", "POSNR", "LIFSP"]);
var_proj = CE_PROJECTION(:var_table,["MBDAT","BMENG","MANDT","VBELN","POSNR","LIFSP",CE_CALC(' leftstr("MBDAT",6) ',VARCHAR(6)) as "PERIODE"]);
var_out = CE_AGGREGATION(:var_proj, [max("PERIODE"), sum("BMENG")],["MANDT","VBELN","POSNR","LIFSP"]);
Activating the Calculation view is now possible but when I try to look at the data preview I get the following error message:
When i remove the attribute in which the maximum of "PERIODE" should be displayed, data preview is possible.
Do you have any idea what is still incorrect. Var_out and output look like this
Thanks a lot and best regards.
Florian
Hi Florian,
Aggregation operators like MAX not always work among revisions.
For your case you still have a chance converting to INTEGER:
var_proj = CE_PROJECTION(:var_table,["MBDAT","BMENG","MANDT","VBELN","POSNR","LIFSP",CE_CALC(' fixed(leftstr("MBDAT",6),6,0) ',INTEGER) as "PERIODE"]);
Sorry about typos, I'm not testing it...
Regards, Fernando Da Rós
User | Count |
---|---|
84 | |
24 | |
12 | |
9 | |
7 | |
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.