topic Re: select Problem in Application Development and Automation Discussions

select Problem

Former Member — Tue, 30 Oct 2007 08:55:59 GMT

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

Re: select Problem

Former Member — Tue, 30 Oct 2007 08:59:41 GMT

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

Re: select Problem

former_member386202 — Tue, 30 Oct 2007 09:00:26 GMT

Hi,

Just check belnr ebeln ebelp and lastly posting date budat.

regards,

Prashant

Re: select Problem

andreas_mann3 — Tue, 30 Oct 2007 09:00:52 GMT

use

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

where ebeln = 45

and ebelp = 10

Re: select Problem

Former Member — Tue, 30 Oct 2007 09:01:02 GMT

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....

Re: select Problem

Former Member — Tue, 30 Oct 2007 09:01:18 GMT

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

Re: select Problem

rainer_hbenthal — Tue, 30 Oct 2007 09:01:22 GMT

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

) .

Re: select Problem

former_member404244 — Tue, 30 Oct 2007 09:02:36 GMT

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..

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

Re: select Problem

Former Member — Tue, 30 Oct 2007 09:03:27 GMT

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.