topic Re: SUBQUERY ( points will be rewarded ) in Application Development and Automation Discussions

SUBQUERY ( points will be rewarded )

Former Member — Tue, 24 Jun 2008 06:46:16 GMT

hello !!

can someone help me to write a query to select all the records from the table ekpo...where the EBELP should be the max for the corresponding PO.

ex. if the ekpo table has the following data ...

EBELN EBELP

4500000003 00010

4500000842 00010

4500000842 00020

4500010022 00010

4500010022 00020

4500010022 00030

4500010022 00040

then my output should be as follows....

EBELN EBELP

4500000003 00010

4500000842 00020

4500010022 00040

thnx in advance ...

Re: SUBQUERY ( points will be rewarded )

Former Member — Tue, 24 Jun 2008 06:56:25 GMT

Hi ,

Use the following code

TABLES:

ekpo.

DATA:

BEGIN OF t_ekpo OCCURS 0,

ebeln TYPE ebeln,

ebelp TYPE ebelp,

END OF t_ekpo.

SELECT-OPTIONS: s_ebeln FOR ekpo-ebeln.

SELECT ebeln

ebelp

FROM ekpo

INTO TABLE t_ekpo

WHERE ebeln IN s_ebeln.

IF sy-subrc EQ 0.

SORT t_ekpo BY ebeln ebelp DESCENDING .

DELETE ADJACENT DUPLICATES FROM t_ekpo COMPARING ebeln.

ENDIF.

Regards,

Jaya Vani

Re: SUBQUERY ( points will be rewarded )

Former Member — Tue, 24 Jun 2008 07:01:07 GMT

Hi Poonam,

you can do like this also for e.g.

***************************************************************

data:itab type table of ekpo.

data:v_ebeln type ekpo-ebeln.

select * from ebelp int table itab where ebeln = v_ebeln.

sort itab by ebeln ebelp descending.

delete adjacent duplicates comparing ebeln.

*****************************************************************

So inthis way your internal table will have PO no. and their highest corresponding line item.

I hope this will help you.

Help children of U.N World Food Program by rewarding points and encourage others to answer your queries.

Re: SUBQUERY ( points will be rewarded )

Former Member — Tue, 24 Jun 2008 07:09:13 GMT

It could be achieved ny using aggregate functions with SELECT statement.

select ebeln

max( DISTINCT ebelp )

from ekpo

into table iekpo

where ebeln = '3000000004' (or specify the entire rangle of EBELN)

group by ebeln .

Re: SUBQUERY ( points will be rewarded )

Former Member — Tue, 24 Jun 2008 07:35:54 GMT

thnx guys.. it was very helpful...i have awarded points as well..thnx again..