Selecting single value using for all entries.

[Former Member]

Hi Experts,

I want to know that is it possible to fetch only the first record for a particular condition while using for all entries.

For ex:

Suppose i got 10 different vbeln from vbak table into my internal table it_vbak. For a particular vbeln there can be multiple records in vbap table.

Now i need to fetch only the first record which is getting from vbap table for different vbeln while using 'for all entries in it_vbak where vbeln = it_vbak-vbeln'. Is it possible?

Thanks in Advance

Be$t!N

Moderator message - Moved to the correct forum

Edited by: Rob Burbank on Nov 17, 2009 9:38 AM

[Former Member]

Hi Experts,

I want to know that is it possible to fetch only the first record for a particular condition while using for all entries.

For ex:

Suppose i got 10 different vbeln from vbak table into my internal table it_vbak. For a particular vbeln there can be multiple records in vbap table.

Now i need to fetch only the first record which is getting from vbap table for different vbeln while using 'for all entries in it_vbak where vbeln = it_vbak-vbeln'. Is it possible?

Thanks in Advance

Be$t!N

Moderator message - Moved to the correct forum

Edited by: Rob Burbank on Nov 17, 2009 9:38 AM

[Former Member]

Hi,

I dont think this is possible using for all entries.

You can select the data into an internal table, sort by vbeln and delete adjacent duplicates by comparing vbeln.

[Former Member]

Hi,

you can not use for all entries to retrive first entry from database table.

only way is looping the itab and fetch using select up to 1 row

Regards,

Nandha

[Former Member]

Hi Bestin,

You can achieve it by using another key field of VBAP Table...

Please try with below coding..

IF IT_VBAK[] IS NOT INITIAL.

SELECT * FROM VBAP
INTO TABLE IT_VBAP
for all entries in it_vbak 
where vbeln = it_vbak-vbeln AND
POSNR = '000010'. " First Line item for particular sale order 

ENDIF.

Hope it will solve your problem..

Thanks & Regards

ilesh 24x7

ilesh Nandaniya

[Former Member]

>

> 
> POSNR = '000010'. " First Line item for particular sale order 

Not sure if this will work since the first item could have been deleted.

Since FOR ALL ENTRIES removed duplicate items from the selection set, the OP might be able to do this by only SELECCTing VBELN from VBAP.

Rob

[Former Member]

Hi Rob Burbank,

Thanks..

You are correct.. If that is the scenario in their company... Again it depends on the configuration and business process.. But it's possible that they may need to delete first on any line item after creation of sale order..

In that case below solution will work..

IF IT_VBAK[] IS NOT INITIAL.
 
SELECT * FROM VBAP
INTO TABLE IT_VBAP
for all entries in it_vbak 
where vbeln = it_vbak-vbeln. 
ENDIF.
SORT IT_VBAP BY VBELN POSNR.
LOOP AT IT_VBAK INTO WA_VBAK.
READ TABLE IT_VBAP INTO WA_VBAP WITH KEY VBELN = WA_VBAK-VBELN.
IF SY-SUBRC = 0.
APPEND WA_VBAP TO IT_VBAP2. " Another Internal table which stores only first record
ENDIF.
CLEAR : WA_VBAP.
ENDLOOP.

OR

IT_VBAP3[] = IT_VBAP[].

SORT IT_VBAP3 BY VBELN POSNR.

DELETE ADJACENT DUPLICATES FROM IT_VBAP3 COMPARING VBELN.

Now Table IT_VBAP2 and IT_VBAP3 will be having only first line items for all sales orders..

Do some little changes in the code as per your requirement.

Hope it will solve your problem..

Thanks & Regards

ilesh 24x7

ilesh Nandaniya

[Former Member]

Hi Guys,

I think we need to do delete adjacent after the fetching.