SELECT MAX statement

[Former Member]

Hi,

I have written a select statement using the SLECT MAX.This statment has to get the Max reocrd from JCDS table where CHGNR is the maximum number.but it is getting only the firsr record instead of the Max record.

SELECT SINGLE MAX( CHGNR ) UDATE

USNAM

INACT FROM JCDS

INTO (WA_JCDS-CHGNR,

WA_JCDS-UDATE,

WA_JCDS-USNAM,

WA_JCDS-INACT)

WHERE OBJNR = WA_AUFK-OBJNR

AND STAT = 'E0003'

GROUP BY CHGNR UDATE USNAM INACT.

Could you please let us know anything wrong with this tatement?

Regards,

Xavier.P

Edited by: Xavier on Jun 8, 2009 12:54 PM

[Former Member]

Use SELECT instead of SELECT SINGLE.

[Former Member]

Hi Xavier

i haven seen any problems in the code .

There might not be any entries for your select condition.

Try this code.

DATA:
wa_aufk TYPE aufk ,
 wa_jcds TYPE jcds.
wa_aufk-objnr = 'OR000000100000'.
SELECT SINGLE MAX( chgnr ) udate
usnam
inact FROM jcds
INTO
(wa_jcds-chgnr,
wa_jcds-udate,
wa_jcds-usnam,
wa_jcds-inact)
WHERE objnr = wa_aufk-objnr
AND stat = 'I0001'"'E0003'
GROUP BY chgnr udate usnam inact.
WRITE:
 wa_jcds-chgnr,
wa_jcds-udate,
wa_jcds-usnam,
wa_jcds-inact.

Regards

Hareesh Menon

[kesavadas_thekkillath]

DATA:wa_aufk TYPE aufk ,
 wa_jcds TYPE jcds.
wa_aufk-objnr = 'OR000000100000'.

SELECT chgnr udate
usnam
inact FROM jcds
INTO table it_jcds
WHERE objnr = wa_aufk-objnr
AND stat = 'I0001'"'E0003'.
if sy-subrc = 0.
sort it_jcds descending by chgnr.
read table it_jcds into wa_jcds index 1.
WRITE: wa_jcds-chgnr,wa_jcds-udate,wa_jcds-usnam,wa_jcds-inact.
endif.

first check whthre the aboce query works.

if its not working then there is no data found in the table

[jaideepsharma]

Hi,

Try using order by chgnr clause in your query.

KR Jaideep,

[Former Member]

Hi,

instead of that you can fetch all the records and sort the internal table by descending order based

on chgnr and fetch record index 1.

Thanks & Regards,

Sateesh.

Edited by: sateesh kumar on Jun 8, 2009 1:09 PM

[Former Member]

If only aggregate expressions are used after SELECT, the results set has one row and the addition GROUP BY is not necessary.

Hence , remove GROUP BY and then try .

Edited by: harsh bhalla on Jun 8, 2009 4:43 PM