topic Re: SELECT MAX statement in Application Development and Automation Discussions

SELECT MAX statement

Former Member — Mon, 08 Jun 2009 10:36:16 GMT

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

Re: SELECT MAX statement

Former Member — Mon, 08 Jun 2009 11:00:27 GMT

Use SELECT instead of SELECT SINGLE.

Re: SELECT MAX statement

Former Member — Mon, 08 Jun 2009 11:01:24 GMT

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

Re: SELECT MAX statement

jaideepsharma — Mon, 08 Jun 2009 11:03:34 GMT

Hi,

Try using order by chgnr clause in your query.

KR Jaideep,

Re: SELECT MAX statement

Former Member — Mon, 08 Jun 2009 11:08:58 GMT

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

Re: SELECT MAX statement

kesavadas_thekkillath — Mon, 08 Jun 2009 11:09:07 GMT




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

Re: SELECT MAX statement

Former Member — Mon, 08 Jun 2009 11:13:02 GMT

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