topic select maximum in Application Development and Automation Discussions

select maximum

Former Member — Thu, 06 May 2010 14:57:34 GMT

hi experts,

SELECT PSPNR

REVISION

BOM_NO

ITEM_NO

UNIT

MATNR

FROM ZMED_GMSDATA INTO TABLE IT_GMS

FOR ALL ENTRIES IN IT_FINAL WHERE pspnr = it_final-pspnr AND DU = IT_FINAL-ZZDU.

here i want to select maximum of DU please help

Re: select maximum

Former Member — Thu, 06 May 2010 15:09:04 GMT

Hi rajashiva

use this code


SELECT PSPNR
REVISION

 MAX( DU )

BOM_NO
ITEM_NO
UNIT
MATNR
FROM ZMED_GMSDATA INTO TABLE IT_GMS
FOR ALL ENTRIES IN IT_FINAL WHERE pspnr = it_final-pspnr AND DU = IT_FINAL-ZZDU

best regards

Marco

Re: select maximum

gastn_jareo — Thu, 06 May 2010 15:16:04 GMT

I don't understand. You want the maximum DU, but you have the clause DU = IT_FINAL-ZZDU.

You want maximum DU in wich group of data? Or you want to use maximum DU in IT_FINAL for each IT_FINAL-POSNR? If this is the case you shoud delete previously records on IT_FINAL wich DU are not the maximum for each POSNR.

But please, clarify your request.

Regards!

Re: select maximum

SuhaSaha — Thu, 06 May 2010 15:20:45 GMT

Hi Marco,

Unfortunately you cannot use any aggregate functions with FAE except COUNT (*). SAP says:

If the addition FOR ALL ENTRIES is used in front of WHERE, or if cluster or pool tables are listed after FROM, no other aggregate expressions apart from COUNT( * ) can be used

@OP: You have to sort your internal table with DU as descending & then DELETE ADJACENT DUPLICATES.

BR,

Suhas

PS: While using FAE always remember to select all the key fields .

Re: select maximum

gastn_jareo — Thu, 06 May 2010 15:38:58 GMT

If you want to select only one record, and that record is the one that has the maximum value of DU then you should sort IT_FINAL by DU descending (as Suhas said) and then select data with this clause:

FROM ZMED_GMSDATA
     UP TO 1 ROWS
     INTO TABLE IT_GMS

Furthermore, as it will give you only one record you can use a structure instead an internal table to store the result.

Regards!