cancel
Showing results for 
Search instead for 
Did you mean: 

left() in CE_FUNCTIONS

Former Member
0 Kudos

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

Accepted Solutions (1)

Accepted Solutions (1)

former_member182114
Active Contributor
0 Kudos

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

Former Member
0 Kudos

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

former_member182114
Active Contributor
0 Kudos

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

Former Member
0 Kudos

Thanks a lot, Fernando!

Answers (0)