select Problem

[Former Member]

Hi All,

I have a requirement in which i have to select belnr field from EKBE table on basis of ebeln and ebelp,but there are multiple records in EKBE table.i have to select the latest belnr number on basis of date field.how can i do that?

For ex

ebeln ebelp belnr date

45 10 001 07/10/2007

45 10 002 09/10/2007

45 10 003 10/10/2007

i want the third record with belnr 003.

thanks in advance

[Former Member]

Hi,

Check the syntax Of Select with Order keyword..

Order by ur required field (date)

Can be helpful..

Thanks,

Praveen

Message was edited by:

praveen parasiya

[former_member386202]

Hi,

Just check belnr ebeln ebelp and lastly posting date budat.

regards,

Prashant

[andreas_mann3]

use

select belnr max( date ) from ekbe into rec...

where ebeln = 45

and ebelp = 10

A.

[Former Member]

You can do it in 2 ways.

First.. get all records in to internal table. Sort the ITAB by date DESCENDING. Now reading the first record with that BELNR. It wil hav less burden on database.

Second, you can get the data by using the <DATE field> DESCENDING in the SELECT statement. But as you are going for aggragate funstions the System will take more time to run this...

i.e. SELECT ....

ORDER BY <DATE FIELD> DESCENDING....

[former_member200338]

Hi,

Say IT_EKBE has the records.

sort it_ekbe by date descending.

read table it_ekbe with key ebeln = <<ur value>>

Reward point if useful,

Regards,

Niyaz

[rainer_hbenthal]

select

ebeln

ebelp

belnr

date

into (....)

from ekbe as o

where date = ( select max(date)

from ekbe

where o~ebeln = ebeln

and o~ebelp = ebelp

and o~belnr = belnr

) .

[former_member404244]

Hi,

U can do it in two ways..either get all the data into internal table and then u can use this ststemnt

sort itab by date descending..Now the latest reocrd will be the first record..

Or

write a select query like this

SELECT single * FROM ekbe into wa_ekbe WHERE ebeln = p_ebeln

and ebelp = p_ebelp

ORDER BY date DESCENDING.

Now u will get only the latest record.

rewrad if helpful.

Regards,

Nagaraj

[Former Member]

Hi,

One way to do this would be to get all the records for a particular ebeln and ebelp and then sort the records based on the date field in descending order.

Once this is done you will have the latest record on top and then use delete adjacent duplicates to delete all the other records except the first one which is the latest one.

Hope it is useful.

Thanks,

Sandeep.