cancel
Showing results for 
Search instead for 
Did you mean: 

the max value for a key in an ODS

Former Member
0 Kudos

hi,

i want to link 2 ODS (Historic PRICE and PRICE)

the key fields in Historic price ODS are : "material", "vendor" , and a field name "AVN"

I want to create a start routine in order to find the data with the maximum value for the field "AVN" among data with the same key "material, vendor"

Once this value found, i want to send it in the ODS : "PRIX"

But how can i do it ?

should i sort the ODS table ? what is the Abap fonction ?

Than you very much for your help.

Jacques

Accepted Solutions (0)

Answers (2)

Answers (2)

Former Member
0 Kudos

You can use an aggregation function into select clause. Look at example below from SAP help.

SELECT scustomname scustompostcode scustom~city

MAX( sbook~loccuram )

INTO (name, postcode, city, max)

FROM scustom INNER JOIN sbook

ON scustomid = sbookcustomid

WHERE sbook~fldate BETWEEN '20010101' AND '20011231' AND

sbook~carrid = 'LH ' AND

sbook~connid = '0400'

GROUP BY scustomname scustompostcode scustom~city

ORDER BY scustom~name.

WRITE: / name, postcode, city, max.

ENDSELECT.

Former Member
0 Kudos

Hi,

seems that you need an extraction via self defined function-module.

Within the FM, you can determine the max value like this:

DATA: itab_ods_hist type standard table of /BIC/A<ODS HISTORIC PRICE>00 with header line.

SELECT * FROM /BIC/A<ODS HISTORIC PRICE>00 INTO CORRESPONDING FIELDS OF TABLE itab_ods_hist

SORT itab_ods_hist descending by material vendor AVN.

DELETE ADJACENT DUPLICATES FORM itab_ods_hist COMPARING material vendor.

  • This will give you only the max values within the itab.

  • now hand this over.

Example for a function module for extraction can be found in the R/3 source system.

hth

cheers

sven